Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s02JP-000FFN-S2 for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 16:51:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1s02IP-000pT5-DS for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 16:50:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s02IO-000pSx-UV for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 16:50:05 +0000 Received: from mail-yw1-x1130.google.com ([2607:f8b0:4864:20::1130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s02IN-0000ec-0m for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 16:50:04 +0000 Received: by mail-yw1-x1130.google.com with SMTP id 00721157ae682-617ddc988f5so12422167b3.2 for ; Thu, 25 Apr 2024 09:50:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714063802; x=1714668602; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=4J6zESARGP7EZ6y69N7dPx/TPBrkIia0j/5I7EfQKXI=; b=VAKLSDAZ8K/DVROmIb6YsAr8TmzomjGFFJ9ir4hhvpav/8ndZwboGVPwffPCgLfxm+ HZcX5X8YNLkSM5xwo4UiagwNyD5ycoB7va/j5cZEcDjhA8OSMBrxWCHguelbcyvz3HS/ kc/Vkt2pbWRfi8jsXVr3Mg3EwfQTWMKF3DgTDZimDlV7zIwgp8fuWHukWnYaUZJMmFg1 W8STfu3KYAwYm3KRzAarLQfcxnoOvnrWCTvso9fLYKaZfUzQ8WiA6S5c08AanADevKjA giXnWYCCXVWYrrwWU9qhLCJmV9NIV1Y801AaoP6c+rpd0cRuXIA0Djr+eLW606RymvA7 0auQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714063802; x=1714668602; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=4J6zESARGP7EZ6y69N7dPx/TPBrkIia0j/5I7EfQKXI=; b=G64fVl8FevLqeCA7CZg9dnCgN+A59dy814/tQZAUNnIXGR0AAAUP/IivhYZDnbPKeZ YopeOCNXJ4UOnV5mophJusasVnOqRRSYQD3ad/aDzxnTfdVCp8ge3tZRdA3H8L4MPI3g 8QqwE5JZ2+9n1ZF99Eq9JsGarS8cMUm/wIfOIlWjrrI5muzkMvIhJAWu090nUFdbPOE4 h0WUBXMXFJlmR5SH77HPPyQ9Vcs93ZY2cte59qa6/mae2ZUJKIxr/3/u6qNYiXChJitr Drzi6Xdk96mERbMCAhDJFc8X+FCWgcRzbDsbpFYccYJKeur8NjmUjXXSFNyMkM8bsIfR oeWA== X-Gm-Message-State: AOJu0Yxm/p7tqG9dPc/t3Ja2rRCwISpohHqEb0sSqSz4rlUDqDngjA0K EdA1dhdXeOOnjouxcYW4IYHti9R+WaQAxhaJ1QBHVtbP+1gjoaGZgbPkEcvdhJSfb1SLZP5728x kkFs907VAuJ8eKeDilnLAd1cBqsA= X-Google-Smtp-Source: AGHT+IGLFIeWZ1nDGHSbXcy0J7Dn4kuiYZSgrYjkXrqRhmTB11ZgWMvUTpiL3wsuiJBhDEfugwMV7Ebkpk9cdX8pJv4= X-Received: by 2002:a05:690c:d86:b0:61a:b3e8:8d94 with SMTP id da6-20020a05690c0d8600b0061ab3e88d94mr83885ywb.0.1714063802167; Thu, 25 Apr 2024 09:50:02 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Pavel Stehule Date: Thu, 25 Apr 2024 18:49:25 +0200 Message-ID: Subject: Re: Need some assistance on stored procedures execution using libpq in C To: Sasmit Utkarsh Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="0000000000000cfbc50616ee9499" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000000cfbc50616ee9499 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =C4=8Dt 25. 4. 2024 v 18:46 odes=C3=ADlatel Sasmit Utkarsh 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=E2=80=AFPM Pavel Stehule > wrote: > >> Hi >> >> =C4=8Dt 25. 4. 2024 v 12:57 odes=C3=ADlatel Sasmit Utkarsh >> 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 f= rom >>> the table using *PQexecPrepared*, even though there are no records for >>> the given input. I see that this message nTuples(rows)=3D1 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)= =3D2 >>> nTuples(rows)=3D1SELECT 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=3D# select * from fs_data;shadow_shc_data=3D# 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_procedureRes= ult >>> 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_procedureRes= ult >>> data type |Argument data types | fa integer, hold boolean, INOUT blk= size >>> integer, INOUT fadata byteaType | proc-[ RECORD 3 >>> ]-------+--------------------------------------------------------------= ---------------------------------------------------Schema >>> | publicName | sql_update_data_procedureRes= ult >>> data type |Argument data types | indata bytea, unhold boolean, fa >>> integerType | proc* >>> >>> Regards, >>> Sasmit Utkarsh >>> +91-7674022625 >>> >> --0000000000000cfbc50616ee9499 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
=C4=8Dt 25. 4. 2024 v=C2=A018:46 odes= =C3=ADlatel Sasmit Utkarsh <u= tkarshsasmit@gmail.com> napsal:
Hi Pavel,

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

<= /div>
extension dblink uses libpq (to connect to other databases or ser= vers). You can check code there

htt= ps://github.com/postgres/postgres/blob/master/contrib/dblink/dblink.c

Regards

Pavel


=
Regards,
Sasmit Utkarsh
+91-7= 674022625


On Thu, Apr 25, 2024 at 8:26=E2= =80=AFPM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hi

<= div class=3D"gmail_quote">
=C4=8Dt 25.= 4. 2024 v=C2=A012:57 odes=C3=ADlatel Sasmit Utkarsh <utkarshsasmit@gmail.com> = napsal:
Hi PostgreSQL Team,

I'm trying to execute t= he stored procedure(details along with the program in the attachment) to fe= tch 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 mes= sage nTuples(rows)=3D1 along with procedure successful execution. How to be= tter handle this?
when it returned probably an empty result set. = Please advise

PQ interface is d= esigned for client server communication

you should= to use SPI API


Regards

Pavel
=C2=A0

Output:
= sasmit@sasmit-Virtual-Machine:~/test$ ./test_procedures
main() Connec= tion to shadow_shc_data database SUCCESSFUL
main() nFields(cols)=3D2 nTuples(rows)=3D1
SELECT opera= tion succeeded on Shadow DB
main() blk_size returned is 7565871
<= /i>

DB:
[nix-shell:/ext/shb/tp= fasm/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=3D# select * from fs_data;
shadow_shc_data=3D# CALL SQL_select_dat= a_procedure(-335509949,false , NULL, NULL);
=C2=A0blksize | fadata
--= -------+--------
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
(1 ro= w)

shadow_shc_data-# \df
List of functions=
-[ RECORD 1 ]-------+--------------------------------------------------= ---------------------------------------------------------------
Schema = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| public
Name =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| sql_insert_data_procedureResult data type =C2=A0 =C2=A0|
Argument data types | fa integer, ft in= teger, ord integer, xaddr text, recid text, blk_size integer, indata bytea,= INOUT outdata bytea
Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| proc
-[ RECORD 2 ]-------+----------------------------------= ---------------------------------------------------------------------------= ----
Schema =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| public
Name =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| sql_select_data_procedure
Result data t= ype =C2=A0 =C2=A0|
Argument data types | fa integer, hold boolean, INOUT= blksize integer, INOUT fadata bytea
Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0| proc
-[ RECORD 3 ]-------+-------------= ---------------------------------------------------------------------------= -------------------------
Schema =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0| public
Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= =C2=A0| sql_update_data_procedure
Result data type =C2=A0 =C2=A0|
Ar= gument data types | indata bytea, unhold boolean, fa integer
Type =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| proc
<= br clear=3D"all">
Regards,
Sasmit Utkarsh
+91-767402262= 5
--0000000000000cfbc50616ee9499--