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

From: Pavel Stehule <[email-masked]>
Date: Thu, 25 Apr 2024 16:56:14 +0200

Hi

čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh <[email-masked]>
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_proceduresmain() Connection
> to shadow_shc_data database SUCCESSFULmain() nFields(cols)=2
> nTuples(rows)=1SELECT operation succeeded on Shadow DBmain() blk_size
> returned is 7565871*
>
> *DB:*
>
>
>
>
>
>
>
>
>
>
> *[nix-shell:/ext/shb/tpfasm/shares-c]$ psql shadow_shc_datapsql (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-# \dfList of functions-[ RECORD 1
> ]-------+-----------------------------------------------------------------------------------------------------------------Schema
>              | publicName                | sql_insert_data_procedureResult
> data type    |Argument data types | fa integer, ft integer, ord integer,
> xaddr text, recid text, blk_size integer, indata bytea, INOUT outdata
> byteaType                | proc-[ RECORD 2
> ]-------+-----------------------------------------------------------------------------------------------------------------Schema
>              | publicName                | sql_select_data_procedureResult
> data type    |Argument data types | fa integer, hold boolean, INOUT blksize
> integer, INOUT fadata byteaType                | proc-[ RECORD 3
> ]-------+-----------------------------------------------------------------------------------------------------------------Schema
>              | publicName                | sql_update_data_procedureResult
> data type    |Argument data types | indata bytea, unhold boolean, fa
> integerType                | proc*
>
> Regards,
> Sasmit Utkarsh
> +91-7674022625
>

---

From: Sasmit Utkarsh <[email-masked]>
Date: Thu, 25 Apr 2024 22:16:22 +0530

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

---

From: Pavel Stehule <[email-masked]>
Date: Thu, 25 Apr 2024 18:49:25 +0200

> of an empty result set using libpq for the given procedure?

extension dblink uses libpq (to connect to other databases or servers). You
can check code there

https://github.com/postgres/postgres/blob/master/contrib/dblink/dblink.c

Regards

Pavel

>>> 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?
>>> *sasmit@sasmit-Virtual-Machine:~/test$ ./test_proceduresmain()
>>> Connection to shadow_shc_data database SUCCESSFULmain() nFields(cols)=2