Обсуждение: [NOVICE] Fwd: PQprepare & Stored Proces & OUT parameters - Volume II
-------- Пересылаемое сообщение--------
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>:
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
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922
-------- Конец пересылаемого сообщения --------
--
С уважением,
Ruslan R. Laishev
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922
Hi !
Has been resolved with : {NULL, "zz", "select p_stv::integer, p_msg::text FROM vcloud.__z$z($1::uuid);",
So , this moment is not enough has been described in the docs.
20.10.2017, 12:04, "Ruslan R. Laishev" <zator@yandex.ru>:
-------- Пересылаемое сообщение--------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$BEGINp_stv = 33;p_msg = 'SS$_NORMAL';END;$body$LANGUAGE 'plpgsql'VOLATILECALLED ON NULL INPUTSECURITY DEFINERLEAKPROOFCOST 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, text20-10-2017 11:55:03.632 23684 [DB\kdepo_db_add_comp\448] type = 23, 2520-10-2017 11:55:03.632 23684 [DB\kdepo_db_add_comp\449] size = 0, 020-10-2017 11:55:03.632 23684 [DB\kdepo_db_add_comp\450] ptr = 0x7890e8, 0x7890e8C: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_msg20-10-2017 11:57:27.166 23773 [DB\kdepo_db_add_comp\449] type = 23, 2520-10-2017 11:57:27.166 23773 [DB\kdepo_db_add_comp\450] size = 4, 1020-10-2017 11:57:27.166 23773 [DB\kdepo_db_add_comp\451] ptr = 0x785768, 0x785770So, 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>: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. LaishevOpenVMS bigot, natural born system/network progger, C contractor.+79013163222+79910009922-------- Конец пересылаемого сообщения ----------С уважением,
Ruslan R. LaishevOpenVMS bigot, natural born system/network progger, C contractor.+79013163222+79910009922
--
С уважением,
Ruslan R. Laishev
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922