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
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 |