0

I have some simple test table in postgres like below:

--DROP TABLE test_point

CREATE TABLE test_point
(
    serie_id   INT NOT NULL,
    version_ts INT NOT NULL,

    PRIMARY KEY (serie_id, version_ts)
);

I try to load a data from it by using COPY TO STDOUT and binary buffers. This is sql definition I use in a test case:

COPY (
                SELECT version_ts
                FROM test_point 
                WHERE 
                    serie_id = $1::int
            ) TO STDOUT (FORMAT binary);

It works ok, if I don't provide any param to bind to in SQL. If I use simple select it recognizes params also as well.

I was trying to provide explicit info about param type during stmt preparation also, but results were similar (it doesn't recognize param).

This is a message I receive during the test case:
0x000001740a288ab0 "ERROR: bind message supplies 1 parameters, but prepared statement \"test1\" requires 0\n"

How to properly provide a param for COPY() statement?

I don't want to cut/concatenate strings for timestamp params and similar types.

Below is a test case showing the issue.

TEST(TSStorage, CopyParamTest)
{
    auto sql = R"(
        COPY (
                SELECT version_ts
                FROM test_point 
                WHERE 
                    serie_id = $1::int
            ) TO STDOUT (FORMAT binary);
      )";

    auto connPtr = PQconnectdb("postgresql://postgres:pswd@localhost/some_db");
    auto result = PQprepare(connPtr, "test1", sql, 0, nullptr);

    // Lambda to test result status
    auto testRes = [&](ExecStatusType status)
    {
        if (PQresultStatus(result) != status)
        {
            PQclear(result);
            auto errorMsg = PQerrorMessage(connPtr);
            PQfinish(connPtr);
            throw std::runtime_error(errorMsg);
        }
    };

    testRes(PGRES_COMMAND_OK);
    PQclear(result);

    int seriesIdParam = htonl(5);

    const char *paramValues[] = {(const char *)&seriesIdParam};
    const int paramLengths[] = {sizeof(seriesIdParam)};
    const int paramFormats[] = {1}; // 1 means binary

    // Execute prepared statement
    result = PQexecPrepared(connPtr,
                                 "test1",
                                 1, //nParams,
                                 paramValues,
                                 paramLengths,
                                 paramFormats,
                                 1); // Output format - binary

    // Ensure it's in COPY_OUT state
    //testRes(PGRES_COPY_OUT);
    if (PQresultStatus(result) != PGRES_COPY_OUT)
    {
        auto errorMsg = PQerrorMessage(connPtr);
        int set_breakpoint_here = 0; // !!! !!! !!!
    }

    PQclear(result);
    PQfinish(connPtr);
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • `auto sql = R"(...` <<-- is this an `int` or a `char*` ? – wildplasser Sep 18 '21 at 12:47
  • It's just string literal (const char* ) – Aleksander Stankiewicz Sep 18 '21 at 12:55
  • I suggest you remove the extra parentheses: `auto sql = R"COPY ...` – wildplasser Sep 18 '21 at 13:10
  • What language is this? What connection library are you using? It should provide a function to correctly quote/escape parameters for their direct inclusion. – jjanes Sep 18 '21 at 15:35
  • https://stackoverflow.com/a/44190617/905902 – wildplasser Sep 18 '21 at 18:56
  • @jjanes: I think it is c++. – wildplasser Sep 18 '21 at 19:14
  • It's c++ with the api provided by libpq. – Aleksander Stankiewicz Sep 18 '21 at 21:55
  • @wildplasser - the question, related to php you provided as a link, has a different context. You can't prepare query plan against unknown table (the param after FROM keywork) – Aleksander Stankiewicz Sep 19 '21 at 09:02
  • @jjanes - I don't want to quote/escape there anythink (and it's not necessary as there is no any hardcoded string param there). I'd like to use native binding. – Aleksander Stankiewicz Sep 19 '21 at 09:06
  • @AleksanderStankiewicz : Laurens gives two reasons. **1)** You cannot use COPY in a prepared statement with parameters. (2 is about table/column names as arguments, which would require dynamic SQL) – wildplasser Sep 19 '21 at 09:16
  • Ok @wildplasser. Laurens wrote that it doesn't work, but didn't write why. In [pg doc](https://www.postgresql.org/docs/current/sql-copy.html) there is no any information that _query_ element in **copy to** syntax doesn't allow binding. You can use query with **where** clause so parametrization through text concatenation works for sure. When you prepare staement it doesn't complain also you use incorrect syntaxt (or disallowed features). It just prepares it with success. I think I have to send this question to pg community also for clarification. Thanks for the hint! :) – Aleksander Stankiewicz Sep 19 '21 at 09:49

0 Answers0