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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-novice by date

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