Thread: Need some assistance on stored procedures execution using libpq in C

Hi PostgreSQL Team,

I'm trying to execute the stored procedure(details along with the program in the attachment) to fetch the records from the table for the inputs given in the code as well. I have already created the procedure in the db. But I see some strange output when I try to fetch the details from the table using PQexecPrepared, even though there are no records for the given input. I see that this message nTuples(rows)=1 along with procedure successful execution. How to better handle this?

when it returned probably an empty result set. Please advise

Output:

sasmit@sasmit-Virtual-Machine:~/test$ ./test_procedures
main() Connection to shadow_shc_data database SUCCESSFUL
main() nFields(cols)=2 nTuples(rows)=1
SELECT operation succeeded on Shadow DB
main() blk_size returned is 7565871

DB:

[nix-shell:/ext/shb/tpfasm/shares-c]$ psql shadow_shc_data
psql (14.9, server 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1))
Type "help" for help.

shadow_shc_data=# select * from fs_data;
shadow_shc_data=# CALL SQL_select_data_procedure(-335509949,false , NULL, NULL);
 blksize | fadata
---------+--------
         |
(1 row)

shadow_shc_data-# \df
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_insert_data_procedure
Result data type    |
Argument data types | fa integer, ft integer, ord integer, xaddr text, recid text, blk_size integer, indata bytea, INOUT outdata bytea
Type                | proc
-[ RECORD 2 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_select_data_procedure
Result data type    |
Argument data types | fa integer, hold boolean, INOUT blksize integer, INOUT fadata bytea
Type                | proc
-[ RECORD 3 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_update_data_procedure
Result data type    |
Argument data types | indata bytea, unhold boolean, fa integer
Type                | proc

Regards,

Sasmit Utkarsh

+91-7674022625

Hi Pavel,

Thanks for the info. But is it not possible to have some kind of handling of an empty result set using libpq for the given procedure? 

Regards,

Sasmit Utkarsh

+91-7674022625

On Thu, Apr 25, 2024 at 8:26 PM Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh <utkarshsasmit@gmail.com> napsal:

Hi PostgreSQL Team,

I'm trying to execute the stored procedure(details along with the program in the attachment) to fetch the records from the table for the inputs given in the code as well. I have already created the procedure in the db. But I see some strange output when I try to fetch the details from the table using PQexecPrepared, even though there are no records for the given input. I see that this message nTuples(rows)=1 along with procedure successful execution. How to better handle this?

when it returned probably an empty result set. Please advise

PQ interface is designed for client server communication

you should to use SPI API

https://www.postgresql.org/docs/current/spi.html

Regards

Pavel

 

Output:

sasmit@sasmit-Virtual-Machine:~/test$ ./test_procedures
main() Connection to shadow_shc_data database SUCCESSFUL
main() nFields(cols)=2 nTuples(rows)=1
SELECT operation succeeded on Shadow DB
main() blk_size returned is 7565871

DB:

[nix-shell:/ext/shb/tpfasm/shares-c]$ psql shadow_shc_data
psql (14.9, server 12.16 (Ubuntu 12.16-0ubuntu0.20.04.1))
Type "help" for help.

shadow_shc_data=# select * from fs_data;
shadow_shc_data=# CALL SQL_select_data_procedure(-335509949,false , NULL, NULL);
 blksize | fadata
---------+--------
         |
(1 row)

shadow_shc_data-# \df
List of functions
-[ RECORD 1 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_insert_data_procedure
Result data type    |
Argument data types | fa integer, ft integer, ord integer, xaddr text, recid text, blk_size integer, indata bytea, INOUT outdata bytea
Type                | proc
-[ RECORD 2 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_select_data_procedure
Result data type    |
Argument data types | fa integer, hold boolean, INOUT blksize integer, INOUT fadata bytea
Type                | proc
-[ RECORD 3 ]-------+-----------------------------------------------------------------------------------------------------------------
Schema              | public
Name                | sql_update_data_procedure
Result data type    |
Argument data types | indata bytea, unhold boolean, fa integer
Type                | proc

Regards,

Sasmit Utkarsh

+91-7674022625