Postgres Pro
Downloads
Home   >   mailing lists

[NOVICE] Fwd: PQprepare & Stored Proces & OUT parameters - Volume II - Mailing list pgsql-novice

From Ruslan R. Laishev
Subject [NOVICE] Fwd: PQprepare & Stored Proces & OUT parameters - Volume II
Date
Msg-id 1295771508490189@web27j.yandex.ru
Whole thread Raw
Responses Re: [NOVICE] Fwd: PQprepare & Stored Proces & OUT parameters - Volume II
List pgsql-novice
Tree view


-------- Пересылаемое сообщение--------
20.10.2017, 12:01, "Ruslan R. Laishev" <zator@yandex.ru>:

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

-------- Конец пересылаемого сообщения --------


-- 
С уважением,
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922

pgsql-novice by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: [NOVICE] PQprepare & Stored Proces & OUT parameters
Next
From: Ruslan R. Laishev
Date:
Subject: Re: [NOVICE] Fwd: PQprepare & Stored Proces & OUT parameters - Volume II