Fwd: PQprepare & Stored Proces & OUT parameters - Volume II

<div><br /></div><div><br /></div><div>-------- Пересылаемое сообщение--------</div><div>20.10.2017, 12:01, "Ruslan R. Laishev" &lt;zator(at)yandex(dot)ru&gt;:</div><div><br /></div><div><div><span style="font-family:courier new,monospace;">Hi All!</span></div><div> </div><div><div><span style="font-family:courier new,monospace;">pgSQL:</span></div><div> </div><div><span style="font-family:courier new,monospace;">CREATE OR REPLACE FUNCTION __z$z (</span></div><div><span style="font-family:courier new,monospace;">  p_compid uuid,</span></div><div><span style="font-family:courier new,monospace;">  out p_stv integer,</span></div><div><span style="font-family:courier new,monospace;">  out p_msg text</span></div><div><span style="font-family:courier new,monospace;">)</span></div><div><span style="font-family:courier new,monospace;">RETURNS record AS</span></div><div><span style="font-family:courier new,monospace;">$body$</span></div><div><span style="font-family:courier new,monospace;">BEGIN</span></div><div><span style="font-family:courier new,monospace;">        p_stv = 33;</span></div><div><span style="font-family:courier new,monospace;">        p_msg = 'SS$_NORMAL';</span></div><div><span style="font-family:courier new,monospace;">END;</span></div><div><span style="font-family:courier new,monospace;">$body$</span></div><div><span style="font-family:courier new,monospace;">LANGUAGE 'plpgsql'</span></div><div><span style="font-family:courier new,monospace;">VOLATILE</span></div><div><span style="font-family:courier new,monospace;">CALLED ON NULL INPUT</span></div><div><span style="font-family:courier new,monospace;">SECURITY DEFINER</span></div><div><span style="font-family:courier new,monospace;">LEAKPROOF</span></div><div><span style="font-family:courier new,monospace;">COST 100;</span></div><div> </div><div><span style="font-family:courier new,monospace;">C:</span></div><div><div><span style="font-family:courier new,monospace;">SQL_QUERY sql_queries [KDEPO$K_SQL_MAX] = {</span></div><div><span style="font-family:courier new,monospace;">    {NULL, "zz", "select <span style="color:#ff0000;"><strong>$2::integer, $3::text</strong></span> FROM vcloud.__z$z($1::uuid);",</span></div><div> </div><div><span style="font-family:courier new,monospace;">...</span></div><div> </div><div><div><span style="font-family:courier new,monospace;">    res = PQexecPrepared(dbctx-&gt;pgconn, psql-&gt;name, psql-&gt;argc, argv, argl, NULL, 1);</span></div><div><span style="font-family:courier new,monospace;">    {</span></div><div><span style="font-family:courier new,monospace;">    status = PQresultStatus(res);</span></div><div><span style="font-family:courier new,monospace;">    $TRACE("PQexecPrepared('%s') -&gt; %d (%s), '%s'",</span></div><div><span style="font-family:courier new,monospace;">             psql-&gt;name, status, PQresStatus(status), PQresultErrorMessage(res));</span></div><div> </div><div><span style="font-family:courier new,monospace;">    $TRACE("name = %s, %s", PQfname(res, 0), PQfname(res, 1));</span></div><div><span style="font-family:courier new,monospace;">    $TRACE("type = %d, %d", PQftype(res, 0), PQftype(res, 1));</span></div><div><span style="font-family:courier new,monospace;">    $TRACE("size = %d, %d", PQgetlength (res, 0, 0), PQgetlength (res, 0, 1));</span></div><div><span style="font-family:courier new,monospace;">    $TRACE("ptr  = %p, %p", PQgetvalue (res, 0, 0), PQgetvalue (res, 0, 1));</span></div><div><span style="font-family:courier new,monospace;">}</span></div></div></div></div><div> </div><div><span style="font-family:courier new,monospace;">...</span></div><div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:55:03.632  23684 [DB\kdepo_db_add_comp\445] PQexecPrepared('zz') -&gt; 2 (PGRES_TUPLES_OK), ''</span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:55:03.632  23684 [DB\kdepo_db_add_comp\447] name = int4, text</span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:55:03.632  23684 [DB\kdepo_db_add_comp\448] type = 23, 25</span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:55:03.632  23684 [DB\kdepo_db_add_comp\449] size = <strong>0, 0</strong></span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:55:03.632  23684 [DB\kdepo_db_add_comp\450] ptr  = <strong>0x7890e8, 0x7890e8</strong></span></div><div> </div><div> </div><div><div><span style="font-family:courier new,monospace;">C:</span></div><div><div><span style="font-family:courier new,monospace;">SQL_QUERY sql_queries [KDEPO$K_SQL_MAX] = {</span></div><div><span style="font-family:courier new,monospace;">    {NULL, "zz", "select <span style="color:#ff0000;"><strong>*</strong></span> FROM vcloud.__z$z($1::uuid);",</span></div><div> </div></div></div><div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:57:27.166  23773 [DB\kdepo_db_add_comp\446] PQexecPrepared('zz') -&gt; 2 (PGRES_TUPLES_OK), ''</span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:57:27.166  23773 [DB\kdepo_db_add_comp\448] name = p_stv, p_msg</span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:57:27.166  23773 [DB\kdepo_db_add_comp\449] type = 23, 25</span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:57:27.166  23773 [DB\kdepo_db_add_comp\450] size = <strong>4, 10</strong></span></div><div><span style="font-family:courier new,monospace;"><span>20-10-2017 11</span>:57:27.166  23773 [DB\kdepo_db_add_comp\451] ptr  = <strong>0x785768, 0x785770</strong></span></div></div></div><div> </div><div> </div><div><span style="font-family:courier new,monospace;"><span style="color:#000000;">So, in case when I use "</span><span style="color:#ff0000;"><strong>$2::integer, $3::text" </strong></span><span style="color:#000000;">- I getting empty or incorrect result</span></span>.</div><div> </div><div> </div><div>TIA.</div><div> </div><div> </div><div> </div><div> </div><div> </div><div> </div><div> </div><div>18.10.2017, 19:26, "David G. Johnston" &lt;<a href="mailto:david(dot)g(dot)johnston(at)gmail(dot)com">david(dot)g(dot)johnston(at)gmail(dot)com</a>&gt;:</div><blockquote type="cite"><div><div style="font-family:arial,helvetica,sans-serif;"><span style="font-family:arial,sans-serif;">On Wed, Oct 18, 2017 at 9:01 AM, Ruslan R. Laishev </span><span style="font-family:arial,sans-serif;">&lt;<a target="_blank" href="mailto:zator(at)yandex(dot)ru">zator(at)yandex(dot)ru</a>&gt;</span><span style="font-family:arial,sans-serif;"> wrote:</span></div><div><div><blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div><div><span style="font-family:courier new,monospace;">No errors if p_stv and p_msg has been declared as IN. So , what I'm need to check ?</span></div></div></blockquote><div> </div><div><div style="font-family:arial,helvetica,sans-serif;">​Input parameters are parameters you have to provide when calling a function.</div></div><div style="font-family:arial,helvetica,sans-serif;"> </div><div style="font-family:arial,helvetica,sans-serif;">SELECT * FROM func($1, $2, $3)  is a function that has three input parameters.​</div><div style="font-family:arial,helvetica,sans-serif;"> </div><div style="font-family:arial,helvetica,sans-serif;"><div>CREATE OR REPLACE FUNCTION __z$z (</div><div>  p_compid uuid,</div><div>  out p_stv integer,</div><div>  out p_msg text</div><div>)</div><div> </div><div>Is a function that has one input parameter.  It would be called like: select p_stv, p_msg FROM func($1)</div><div> </div><div>David J.</div></div></div></div></div></blockquote><div> </div><div> </div><div>-- </div><div>С уважением,<br />Ruslan R. Laishev</div><div>OpenVMS bigot, natural born system/network progger, C contractor.</div><div><span>+79013163222</span></div><div><span>+79910009922</span></div><div> </div></div><div><br /></div><div>-------- Конец пересылаемого сообщения --------</div><div><br /></div><div><br /></div><div>-- </div><div>С уважением,<br /> Ruslan R. Laishev</div><div>OpenVMS bigot, natural born system/network progger, C contractor.</div><div>+79013163222</div><div>+79910009922</div><div><br /></div>