Thread: Functions returns to libpq with wrong column split
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
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
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
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
> > "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