MIME-Version: 1.0
From: Sasmit Utkarsh <utkarshsasmit@gmail.com>
Date: Thu, 25 Apr 2024 16:27:13 +0530
Message-ID: <CAM-5MT2-Sr8CquG96KWfz05b1smh4giP8xisyiT1qxL5vqpgiQ@mail.gmail.com>
Subject: Need some assistance on stored procedures execution using libpq in C
To: pgsql-general@lists.postgresql.org
Content-Type: multipart/mixed; boundary="000000000000ff824f0616e9a629"
Archived-At: <https://www.postgresql.org/message-id/CAM-5MT2-Sr8CquG96KWfz05b1smh4giP8xisyiT1qxL5vqpgiQ%40mail.gmail.com>
Precedence: bulk

--000000000000ff824f0616e9a629
Content-Type: multipart/alternative; boundary="000000000000ff824e0616e9a627"

--000000000000ff824e0616e9a627
Content-Type: text/plain; charset="UTF-8"

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

--000000000000ff824e0616e9a627
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi PostgreSQL Team,<div><br></div><div>I&#39;m trying to e=
xecute the stored procedure(details along with the program in the attachmen=
t) 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 strang=
e output when I try to fetch the details from the table using <b>PQexecPrep=
ared</b>, even though there are no records for the given input. I see that =
this message nTuples(rows)=3D1 along with procedure successful execution. H=
ow to better handle this?</div><div>when it returned probably an empty resu=
lt set. Please advise</div><div><br></div><div><b>Output:</b></div><div><i>=
sasmit@sasmit-Virtual-Machine:~/test$ ./test_procedures<br>main() Connectio=
n to shadow_shc_data database SUCCESSFUL<br><b style=3D"background-color:rg=
b(255,255,0)">main() nFields(cols)=3D2 nTuples(rows)=3D1<br>SELECT operatio=
n succeeded on Shadow DB<br>main() blk_size returned is 7565871</b><br></i>=
</div><div><br></div><div><b>DB:</b></div><div><i>[nix-shell:/ext/shb/tpfas=
m/shares-c]$ psql shadow_shc_data<br>psql (14.9, server 12.16 (Ubuntu 12.16=
-0ubuntu0.20.04.1))<br>Type &quot;help&quot; for help.<br><br>shadow_shc_da=
ta=3D# select * from fs_data;<br><span style=3D"background-color:rgb(255,25=
5,0)"><b><font color=3D"#000000">shadow_shc_data=3D# CALL SQL_select_data_p=
rocedure(-335509949,false , NULL, NULL);<br>=C2=A0blksize | fadata<br>-----=
----+--------<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0|</font></b><br>(1 row)<=
/span><br></i></div><div><span style=3D"background-color:rgb(255,255,0)"><i=
><br></i></span></div><div><i>shadow_shc_data-# \df<br>List of functions<br=
>-[ RECORD 1 ]-------+-----------------------------------------------------=
------------------------------------------------------------<br>Schema =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| public<br>Name =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| sql_insert_data_procedure<br>Re=
sult data type =C2=A0 =C2=A0|<br>Argument data types | fa integer, ft integ=
er, ord integer, xaddr text, recid text, blk_size integer, indata bytea, IN=
OUT outdata bytea<br>Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0| proc<br>-[ RECORD 2 ]-------+--------------------------------------=
---------------------------------------------------------------------------=
<br><b style=3D"background-color:rgb(255,255,0)">Schema =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| public<br>Name =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0 =C2=A0 =C2=A0| sql_select_data_procedure<br>Result data type=
 =C2=A0 =C2=A0|<br>Argument data types | fa integer, hold boolean, INOUT bl=
ksize integer, INOUT fadata bytea<br>Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0| proc</b><br>-[ RECORD 3 ]-------+----------------=
---------------------------------------------------------------------------=
----------------------<br>Schema =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0| public<br>Name =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0| sql_update_data_procedure<br>Result data type =C2=A0 =C2=A0|<br>Argume=
nt data types | indata bytea, unhold boolean, fa integer<br>Type =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| proc</i><br></div><div><br c=
lear=3D"all"><div><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=
=3D"gmail_signature"><div dir=3D"ltr"><div>Regards,<br></div><div>Sasmit Ut=
karsh</div><div>+91-7674022625</div></div></div></div></div></div>

--000000000000ff824e0616e9a627--
--000000000000ff824f0616e9a629
Content-Type: text/plain; charset="US-ASCII"; name="test_procedures.c"
Content-Disposition: attachment; filename="test_procedures.c"
Content-Transfer-Encoding: base64
Content-ID: <f_lvf4mbex0>
X-Attachment-Id: f_lvf4mbex0

LyoqKiBzdG9yZWQgUHJvY2VkdXJlOgoKQ1JFQVRFIE9SIFJFUExBQ0UgUFJPQ0VEVVJFIHNxbF9z
ZWxlY3RfZGF0YV9wcm9jZWR1cmUoCiAgICBmYSBpbnRlZ2VyLAogICAgaG9sZCBib29sLAogICAg
SU5PVVQgYmxrc2l6ZSBpbnRlZ2VyLAogICAgSU5PVVQgZmFkYXRhIGJ5dGVhCikgTEFOR1VBR0Ug
cGxwZ3NxbCBBUyAKJCQgCkJFR0lOIAogICAgSUYgKGhvbGQpIFRIRU4KICAgICAgICBQRVJGT1JN
IHBnX2Fkdmlzb3J5X2xvY2soZmEpOwogICAgRU5EIElGOyAKICAgIFNFTEVDVCBibGtfc2l6ZSwg
ZGF0YSBJTlRPIGJsa3NpemUsIGZhZGF0YSAKICAgIEZST00gZnNfZGF0YSAKICAgIFdIRVJFIGZp
bGVfYWRkcmVzcyA9IGZhOyAKRU5EOyAKJCQ7CgoqKiovCiNpbmNsdWRlIDxzdGRpby5oPgojaW5j
bHVkZSA8c3RkbGliLmg+CiNpbmNsdWRlIDxzdGRib29sLmg+CiNpbmNsdWRlIDxsaWJwcS1mZS5o
PgojaW5jbHVkZSA8YXJwYS9pbmV0Lmg+IC8vIEZvciBodG9ubAoKaW50IG1haW4oKSB7CgogICAg
aW50ICAgbkZpZWxkcyA9IDA7CiAgICBpbnQgICBuVHVwbGVzID0gMDsKICAgIGludCAgIGJsa19z
aXplID0gMDsKICAgIFBHY29ubiAqc2hhZG93X2RiX2Nvbm47CiAgICBQR3Jlc3VsdCAqcmVzOwog
ICAgY29uc3QgY2hhciAqc2hhZG93X2RiX2Nvbm5faW5mbyA9ICJkYm5hbWU9c2hhZG93X3NoY19k
YXRhIjsKICAgIGludCBpc19zaGFkb3dfZGIgPSAxOyAvLyBBc3N1bWluZyBpdCdzIGEgc2hhZG93
IGRhdGFiYXNlCiAgICBjaGFyICpibGtfc2l6ZV92YWw7CiAgICBjaGFyICpkYXRhX3ZhbDsKCiAg
ICAvLyBDb25uZWN0IHRvIHRoZSBzaGFkb3cgZGF0YWJhc2UKICAgIGlmIChpc19zaGFkb3dfZGIp
IHsKICAgICAgICBzaGFkb3dfZGJfY29ubiA9IFBRY29ubmVjdGRiKHNoYWRvd19kYl9jb25uX2lu
Zm8pOwogICAgICAgIGlmIChQUXN0YXR1cyhzaGFkb3dfZGJfY29ubikgIT0gQ09OTkVDVElPTl9P
SykgewogICAgICAgICAgICBmcHJpbnRmKHN0ZGVyciwgIkNvbm5lY3Rpb24gdG8gc2hhZG93IGRh
dGFiYXNlIGZhaWxlZDogJXNcbiIsIFBRZXJyb3JNZXNzYWdlKHNoYWRvd19kYl9jb25uKSk7CiAg
ICAgICAgICAgIFBRZmluaXNoKHNoYWRvd19kYl9jb25uKTsKICAgICAgICAgICAgZXhpdCgxKTsK
ICAgICAgICB9CgogICAgICAgIC8vIFNldCBieXRlYV9vdXRwdXQgdG8gJ2VzY2FwZScKICAgICAg
ICByZXMgPSBQUWV4ZWMoc2hhZG93X2RiX2Nvbm4sICJTRVQgYnl0ZWFfb3V0cHV0ID0gJ2VzY2Fw
ZSciKTsKICAgICAgICBpZiAoUFFyZXN1bHRTdGF0dXMocmVzKSAhPSBQR1JFU19DT01NQU5EX09L
KSB7CiAgICAgICAgICAgIGZwcmludGYoc3RkZXJyLCAiU0VUIGJ5dGVhX291dHB1dCBjb21tYW5k
IGZhaWxlZDogJXNcbiIsIFBRZXJyb3JNZXNzYWdlKHNoYWRvd19kYl9jb25uKSk7CiAgICAgICAg
ICAgIFBRY2xlYXIocmVzKTsKICAgICAgICAgICAgUFFmaW5pc2goc2hhZG93X2RiX2Nvbm4pOwog
ICAgICAgICAgICBleGl0KDEpOwogICAgICAgIH0KICAgICAgICBQUWNsZWFyKHJlcyk7CiAgICAg
ICAgcHJpbnRmKCIlcygpIENvbm5lY3Rpb24gdG8gc2hhZG93X3NoY19kYXRhIGRhdGFiYXNlIFNV
Q0NFU1NGVUxcbiIsIF9fZnVuY19fKTsKICAgIH0KCiAgICAvLyBDb25zdHJ1Y3QgdGhlIGNvbW1h
bmQgdG8gcHJlcGFyZQogICAgY2hhciBTZWxlY3REYXRhTmFtZVsxMV07CiAgICBjaGFyIFNlbGVj
dERhdGFDb21tYW5kWzE1MF07CiAgICBpbnQgIFNlbGVjdERhdGFOUGFyYW1zOwogICAgT2lkICBT
ZWxlY3REYXRhUGFyYW1UeXBlc1syXTsKCiAgICBzcHJpbnRmKFNlbGVjdERhdGFOYW1lLCIlcyIs
IlNlbGVjdERhdGEiKTsKICAgIHNwcmludGYoU2VsZWN0RGF0YUNvbW1hbmQsICJDQUxMIFNRTF9z
ZWxlY3RfZGF0YV9wcm9jZWR1cmUoJDEsICQyLCBOVUxMLCBOVUxMKSIpOwogICAgU2VsZWN0RGF0
YU5QYXJhbXMgPSAyOwogICAgU2VsZWN0RGF0YVBhcmFtVHlwZXNbMF0gPSAyMzsgIC8vIGludAog
ICAgU2VsZWN0RGF0YVBhcmFtVHlwZXNbMV0gPSAxNjsgIC8vIGJvb2wKCiAgICAvLyBQcmVwYXJl
IHRoZSBzdGF0ZW1lbnQKICAgIGlmIChpc19zaGFkb3dfZGIpIHsKICAgICAgICByZXMgPSBQUXBy
ZXBhcmUoc2hhZG93X2RiX2Nvbm4sCiAgICAgICAgICAgICAgICAgICAgICAgIFNlbGVjdERhdGFO
YW1lLAogICAgICAgICAgICAgICAgICAgICAgICBTZWxlY3REYXRhQ29tbWFuZCwKICAgICAgICAg
ICAgICAgICAgICAgICAgU2VsZWN0RGF0YU5QYXJhbXMsCiAgICAgICAgICAgICAgICAgICAgICAg
IFNlbGVjdERhdGFQYXJhbVR5cGVzKTsKICAgICAgICBpZiAoUFFyZXN1bHRTdGF0dXMocmVzKSAh
PSBQR1JFU19DT01NQU5EX09LKSB7CiAgICAgICAgICAgIGZwcmludGYoc3RkZXJyLCAiUHJlcGFy
ZSBmYWlsZWQ6ICVzXG4iLCBQUWVycm9yTWVzc2FnZShzaGFkb3dfZGJfY29ubikpOwogICAgICAg
ICAgICBQUWNsZWFyKHJlcyk7CiAgICAgICAgICAgIFBRZmluaXNoKHNoYWRvd19kYl9jb25uKTsK
ICAgICAgICAgICAgZXhpdCgxKTsKICAgICAgICB9CiAgICAgICAgUFFjbGVhcihyZXMpOwogICAg
fQoKICAgIC8vIFNhbXBsZSBpbnB1dCB2YWx1ZXMKICAgIGludDMyX3QgZmEgPSAtMzM1NTA5OTQ5
OwogICAgYm9vbCBob2xkID0gZmFsc2U7CgogICAgdWludDMyX3QgZmEyID0gaHRvbmwoZmEpOwog
ICAgY2hhciAqZmFfdmFsID0gKGNoYXIgKikmZmEyOwogICAgYm9vbCBob2xkMiA9IGhvbGQ7CiAg
ICBjaGFyICpob2xkX3ZhbCA9IChjaGFyICopJmhvbGQyOwoKCiAgICBjb25zdCBjaGFyICpwYXJh
bVZhbHVlc1syXSAgICAgID0ge2ZhX3ZhbCwgaG9sZF92YWx9OwogICAgaW50ICAgcGFyYW1MZW5n
dGhzWzJdICAgICAgICAgICA9IHtzaXplb2YoZmEyKSwgc2l6ZW9mKGhvbGQyKX07CiAgICBpbnQg
ICBwYXJhbUZvcm1hdHNbMl0gICAgICAgICAgID0gezEsIDF9OyAgICAgICAvLyBpZGVudGlmeSBw
YXJhbWV0ZXIgYXMgYmluYXJ5CgogICAgLy8gQXNzdW1pbmcgcGFyYW1WYWx1ZXMsIHBhcmFtTGVu
Z3RocywgYW5kIHBhcmFtRm9ybWF0cyBhcmUgcHJvcGVybHkgcG9wdWxhdGVkCiAgICAvLyBFeGVj
dXRlIHRoZSBwcmVwYXJlZCBzdGF0ZW1lbnQKICAgIGlmIChpc19zaGFkb3dfZGIpIHsKICAgICAg
ICAvL2NvbnN0IGNoYXIgKnBhcmFtVmFsdWVzWzJdID0geyIxMjMiLCAidHJ1ZSJ9OwogICAgICAg
IC8vaW50IHBhcmFtTGVuZ3Roc1syXSA9IHtzaXplb2YoIjEyMyIpIC0gMSwgc2l6ZW9mKCJ0cnVl
IikgLSAxfTsgLy8gTWludXMgMSBmb3IgZXhjbHVkaW5nIG51bGwgdGVybWluYXRvcgogICAgICAg
IC8vaW50IHBhcmFtRm9ybWF0c1syXSA9IHswLCAwfTsgLy8gQXNzdW1pbmcgYWxsIHBhcmFtZXRl
cnMgYXJlIGluIHRleHQgZm9ybWF0CiAgICAgICAgaW50IHJlc3VsdEZvcm1hdCA9IDE7IC8vIDEg
Zm9yIGJpbmFyeSByZXN1bHQgZm9ybWF0CgogICAgICAgIHJlcyA9IFBRZXhlY1ByZXBhcmVkKHNo
YWRvd19kYl9jb25uLAogICAgICAgICAgICAgICAgICAgICAgICAgICAgIFNlbGVjdERhdGFOYW1l
LAogICAgICAgICAgICAgICAgICAgICAgICAgICAgIFNlbGVjdERhdGFOUGFyYW1zLAogICAgICAg
ICAgICAgICAgICAgICAgICAgICAgIHBhcmFtVmFsdWVzLAogICAgICAgICAgICAgICAgICAgICAg
ICAgICAgIHBhcmFtTGVuZ3RocywKICAgICAgICAgICAgICAgICAgICAgICAgICAgICBwYXJhbUZv
cm1hdHMsCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgcmVzdWx0Rm9ybWF0KTsKCiAgICAg
ICAgaWYgKFBRcmVzdWx0U3RhdHVzKHJlcykgPT0gUEdSRVNfVFVQTEVTX09LICYmIFBRbmZpZWxk
cyhyZXMpICE9IDAgJiYgUFFudHVwbGVzKHJlcykgPiAwKSB7CiAgICAgICAgICAgIC8vcHJpbnRm
KCJTRUxFQ1Qgb3BlcmF0aW9uIHN1Y2NlZWRlZCBvbiBTaGFkb3cgREJcbiIpOwogICAgICAgICAg
ICAvLyBQcm9jZXNzIHRoZSByZXN1bHQgaWYgbmVlZGVkCiAgICAgICAgICAgIG5GaWVsZHMgPSBQ
UW5maWVsZHMocmVzKTsgICAgICAgLy8gbnVtYmVyIG9mIGNvbHVtbnMKICAgICAgICAgICAgblR1
cGxlcyA9IFBRbnR1cGxlcyhyZXMpOyAgICAgICAvLyBudW1iZXIgb2Ygcm93cwogICAgICAgICAg
ICBwcmludGYoIiVzKCkgbkZpZWxkcyhjb2xzKT0laSBuVHVwbGVzKHJvd3MpPSVpXG4iLF9fZnVu
Y19fLG5GaWVsZHMsblR1cGxlcyk7CgogICAgICAgICAgICBibGtfc2l6ZV92YWwgPSBQUWdldHZh
bHVlKHJlcywgMCwgMCk7CiAgICAgICAgICAgIGRhdGFfdmFsID0gUFFnZXR2YWx1ZShyZXMsIDAs
IDEpOwogICAgICAgICAgICBpZihibGtfc2l6ZV92YWwgIT0gTlVMTCAmJiBkYXRhX3ZhbCAhPSBO
VUxMKQogICAgICAgICAgICB7CiAgICAgICAgICAgICAgICAgIHByaW50ZigiU0VMRUNUIG9wZXJh
dGlvbiBzdWNjZWVkZWQgb24gU2hhZG93IERCXG4iKTsKICAgICAgICAgICAgICAgICAgYmxrX3Np
emUgPSBudG9obCgqKHVpbnQzMl90ICopUFFnZXR2YWx1ZSAocmVzLCAwLCAwKSk7CiAgICAgICAg
ICAgICAgICAgIHByaW50ZigiJXMoKSBibGtfc2l6ZSByZXR1cm5lZCBpcyAlaVxuIixfX2Z1bmNf
XyxibGtfc2l6ZSk7CiAgICAgICAgICAgIH0KCiAgICAgICAgfSBlbHNlIHsKICAgICAgICAgICAg
ZnByaW50ZihzdGRlcnIsICJTRUxFQ1QgZmFpbGVkIG9uIFNoYWRvdyBEQjogJXNcbiIsIFBRZXJy
b3JNZXNzYWdlKHNoYWRvd19kYl9jb25uKSk7CiAgICAgICAgfQogICAgICAgIFBRY2xlYXIocmVz
KTsKICAgIH0KCiAgICBQUWZpbmlzaChzaGFkb3dfZGJfY29ubik7IC8vIENsb3NlIHRoZSBjb25u
ZWN0aW9uCiAgICByZXR1cm4gMDsKfQoK
--000000000000ff824f0616e9a629--