Thread: Functions returns to libpq with wrong column split

Functions returns to libpq with wrong column split

From
Andy Halsall
Date:
Could someone please help with me with stored procedures and the libpq api?
 
I have two simple tables that can be joined on a common bigint node_id. Both tables also have a bytea data column.
I'm querying with PQexecparams() to a stored procedure and requesting that the results are returned in  binary data.
 
My stored procedure looks like this:

 CREATE OR REPLACE FUNCTION readnode4(bigint) RETURNS  appexcatdata AS '
 declare
 h appexcatdata;
 begin
 select INTO h r.user_data, n.system_data FROM relationship r INNER JOIN node_system n ON n.node_id =  r.node_id WHERE r.node_id = $1 LIMIT 1;
 RETURN h;
 end
 '
 language 'plpgsql';
 
Where the type appexcatdata is defined to be:
 create type appexcatdata as (sys bytea, usr bytea);
 
The following code makes the query:
        uint64_t big_rndn = 1;    //rndn;
        uint64_t big_guid = htonll(big_rndn);
        const char* paramValues[1];
        paramValues[0] = (const char*)&big_guid;
        int paramLengths[1];
        paramLengths[0] = 8;
        int paramFormats[1];
        paramFormats[0] = 1;
        res = PQexecParams(conn,
                           "SELECT readnode4($1)",
                            1,
                            NULL,    // paramTypes,
                            paramValues,
                            paramLengths,
                            paramFormats,
                            1);
        if(PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            printf("SELECT failed: %s\n", PQerrorMessage(conn));
            PQclear(res);
            PQfinish(conn);
            exit(1);
        }
       printf("num rows =%d\n", PQntuples(res));
       printf("num columns =%d\n", PQnfields(res));
       printf("size of col 0=%d\n", PQfsize(res,0));
       unsigned char* data = (unsigned char*) PQgetvalue(res, 0, 0);
       for(int i=0; i < 1000; i++)
       {
           printf("%x", data[i]);
       }
       printf("\n");
 
And I get a single row with one column containing all the data (I think):
num rows =1
num columns =1
size of col 0=-1
000200011000226331666666666666666666666666666666663030303030303030303030303030303000011003e8
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
........ snipped
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
30313233343536373839303132333435363738393031323334353637383930313233343536373839303132333435
Columns:
readnode4
 
 
When I query the SP using text through PQExec as follows:
        sprintf(query_string,"SELECT readnode4(%d)",i);
        res = PQexec(conn,query_string);
        if(PQresultStatus(res) != PGRES_TUPLES_OK)
        {
            printf("SELECT failed: %s\n", PQerrorMessage(conn));
            PQclear(res);
            PQfinish(conn);
            exit(1);
        }
        printf("num rows =%d\n", PQntuples(res));
        printf("num columns =%d\n", PQnfields(res));
        printf("size of col 0=%d\n", PQfsize(res,0));
       char* data = PQgetvalue(res, 0, 0);
       printf("Data length = %d\nData = %s\n", strlen(data),data);
I get a single row and column with all the data but this time clearly separated:
 
num rows =1
num columns =1
size of col 0=-1
Data length = 2081
Data = (
"\\x63316666666666666666666666666666666630303030303030303030303030303030",
"\\x30313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
...... snipped
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
3031323334353637383930313233343536373839303132333435363738393031323334353637383930313233343536373839
30313233343536373839")
 
 
It seems like my stored procedure is failing to split the results into columns. Have tried various changes to no avail. Can anyone help please?

Thanks,
Andy

Re: Functions returns to libpq with wrong column split

From
Tom Lane
Date:
Andy Halsall <halsall_andy@hotmail.com> writes:
> My stored procedure looks like this:

>  CREATE OR REPLACE FUNCTION readnode4(bigint) RETURNS  appexcatdata AS '
>  declare
>  h appexcatdata;
>  begin
>  select INTO h r.user_data, n.system_data FROM relationship r INNER JOIN node_system n ON n.node_id =  r.node_id
WHEREr.node_id = $1 LIMIT 1; 
>  RETURN h;
>  end
>  '
>  language 'plpgsql';

[ which is queried as ]

>                            "SELECT readnode4($1)",

I think you probably want "SELECT * FROM readnode4($1)".  As-is,
you're getting a single composite-type column from the SELECT.

            regards, tom lane

Re: Functions returns to libpq with wrong column split

From
Andy Halsall
Date:
That's fixed it. Many thanks Tom.
 
> > "SELECT readnode4($1)",
>
> I think you probably want "SELECT * FROM readnode4($1)". As-is,
> you're getting a single composite-type column from the SELECT.
>
> regards, tom lane