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

čt 25. 4. 2024 v 18:46 odesílatel Sasmit Utkarsh <utkarshsasmit(at)gmail(dot)com>
napsal:

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

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

>
>
> Regards,
> Sasmit Utkarsh
> +91-7674022625
>
>
> On Thu, Apr 25, 2024 at 8:26 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>> Hi
>>
>> čt 25. 4. 2024 v 12:57 odesílatel Sasmit Utkarsh <utkarshsasmit(at)gmail(dot)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(at)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
>>>
>>