Skip site navigation (1) Skip section navigation (2)

Functions returns to libpq with wrong column split

From: Andy Halsall <halsall_andy(at)hotmail(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Functions returns to libpq with wrong column split
Date: 2011-10-18 16:23:25
Message-ID: BLU123-W983C2DD7A8FCA13F5D69FF5E50@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-novice
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 		 	   		  

Responses

pgsql-novice by date

Next:From: Dournaee, BlakeDate: 2011-10-18 19:41:52
Subject: enterprise support
Previous:From: Daniel StaalDate: 2011-10-18 13:52:56
Subject: Re: last row of table after csv import

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group