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 1s02FA-000EYp-FJ for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 16:46:44 +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 1s02F7-000lz1-UZ for pgsql-general@arkaria.postgresql.org; Thu, 25 Apr 2024 16:46:42 +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 1s02F7-000lyq-C4 for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 16:46:42 +0000 Received: from mail-pg1-x52b.google.com ([2607:f8b0:4864:20::52b]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1s02F1-0000dT-8c for pgsql-general@lists.postgresql.org; Thu, 25 Apr 2024 16:46:41 +0000 Received: by mail-pg1-x52b.google.com with SMTP id 41be03b00d2f7-5d42e7ab8a9so721315a12.3 for ; Thu, 25 Apr 2024 09:46:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1714063594; x=1714668394; 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=rlYuHcHkm+lFiZlyMttWbTwQS1UHlER0V9XLkUMUZdw=; b=XojPfcHlACtp9CKhhj8vmZfzBS02xPxUZEOqvSTsPtuj5hG4ri7RdIV08q7qhQIIkv Eld2fkG/JTpKC+euJVKqGcMWmn5d5m1OS+zDBv19MhbE+EMWizSwWwRchXj3lG8qDSQ4 HClE3p6PH82RZqTFggrXuEEkdfXoMj90dyeO3VbVD5iX/2BPJ5QL3IBh79HyyOoGvK5/ AvfpM8oJLz0YpRoVPM2IJb8eqqvqvFjiI+MQmYNHQIUxJWm72mzpcLJdifp87kLPGrre DCYMf8HVcm2+iQlS/R8BnPfOSLzQOZxm5gYLoivAEqLaTnqAokXmLOsMOQF9Net5lmAx kDUA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1714063594; x=1714668394; 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=rlYuHcHkm+lFiZlyMttWbTwQS1UHlER0V9XLkUMUZdw=; b=OVQPRooD9r9UCxuueQWMga9hKg1VFr2eG4JwucvMbF5ejq10mNyNJW8dAWJ80jTKk+ tgyFGcuiZNJWCwyehS6b9wFQQRq/cHxbNZaSvx4jN7vkgcE04lQiH9O4lUft8RU2Ihaq nWJD4mGRfc3NQTcUX+zOyGIKRdsqt2fc0TUeEU+nHN/3XU/FTvt/i1m4IFe2BGLQkWr6 uLmVHRnnJM/qp1i7h1xfQzWmAAIGzBjzGBbVt4J3+zTzJCHYFftnEyQcnyBS4R/L/suv 23DHS2S+01VvPwaiytZiOFtjIgI3YAYZPZHWHL9CGKAF8kyzrm9tM7dwfQlJuD23neqU 0x7Q== X-Gm-Message-State: AOJu0YzLzjNFX4UMlRMSIZkE+QMKlx85I9fcp6KGHA23BiBzfu1RaaFi mamtJmdi1O5D6fKzV4UemQ+689wGzYqwiykvnW9PyaFRpeOm97n2DBr9vs4Y8HSAEmp9hcl1jXI nKrTDSKa6SVB+ez5qb2bZlAGFGbY= X-Google-Smtp-Source: AGHT+IHj7H9v80mpcWbhMji9bx/CvQkDJh6der/ovKDDhQFPvGKwOk6hmfYgVAcKx+LBwAcUOJM1eYISTtMwMxYk7iE= X-Received: by 2002:a17:90b:1d01:b0:2af:3c0e:f6b8 with SMTP id on1-20020a17090b1d0100b002af3c0ef6b8mr123607pjb.3.1714063594051; Thu, 25 Apr 2024 09:46:34 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Sasmit Utkarsh Date: Thu, 25 Apr 2024 22:16:22 +0530 Message-ID: Subject: Re: Need some assistance on stored procedures execution using libpq in C To: Pavel Stehule Cc: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000a55ee60616ee8761" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000a55ee60616ee8761 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=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 progra= m >> in the attachment) to fetch the records from the table for the inputs gi= ven >> 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)=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() Connectio= n >> 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_procedureResu= lt >> 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_procedureResu= lt >> data type |Argument data types | fa integer, hold boolean, INOUT blks= ize >> integer, INOUT fadata byteaType | proc-[ RECORD 3 >> ]-------+---------------------------------------------------------------= --------------------------------------------------Schema >> | publicName | sql_update_data_procedureResu= lt >> data type |Argument data types | indata bytea, unhold boolean, fa >> integerType | proc* >> >> Regards, >> Sasmit Utkarsh >> +91-7674022625 >> > --000000000000a55ee60616ee8761 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Pavel,

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


Regards,
Sasmit Utkarsh
+91-767402= 2625


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

=C4=8Dt 25. 4. 2024 v=C2=A012:57 od= es=C3=ADlatel Sasmit Utkarsh <utkarshsasmit@gmail.com> napsal:
Hi PostgreSQL T= eam,

I'm trying to execute the stored procedure(deta= ils 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)=3D1 a= long with procedure successful execution. How to better handle this?
<= div>when it returned probably an empty result set. Please advise

PQ interface is designed for client serve= r communication

you should to use SPI API

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

Regards

Pavel
= =C2=A0

Output:
sasmit@sasmit-Virt= ual-Machine:~/test$ ./test_procedures
main() Connection to shadow_shc_da= ta database SUCCESSFUL
main= () nFields(cols)=3D2 nTuples(rows)=3D1
SELECT operation succeeded on Sha= dow 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=3D# select * fr= om fs_data;
shadow_shc_data=3D# CALL SQL_select_data_procedure(-33550994= 9,false , NULL, NULL);
=C2=A0blksize | fadata
---------+--------
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|
(1 row)

<= /div>
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_procedure
Result data type =C2= =A0 =C2=A0|
Argument data types | fa integer, ft integer, ord integer, x= addr text, recid text, blk_size integer, indata bytea, INOUT outdata bytea<= br>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 type =C2=A0 =C2=A0|Argument data types | fa integer, hold boolean, INOUT blksize integer, INO= UT 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_da= ta_procedure
Result data type =C2=A0 =C2=A0|
Argument data types | in= data bytea, unhold boolean, fa integer
Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0| proc

=
Regards,
Sasmit Utkarsh
+91-7674022625
--000000000000a55ee60616ee8761--