Thread: [NOVICE] PQprepare & Stored Proces & OUT parameters
Hi All!
Ask for help in a yet another puzzle:
-----------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION __z$z (
p_compid uuid,
out p_stv integer,
out p_msg text
)
RETURNS void AS
$body$
BEGIN
END;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;
-----------------------------------------------------------------------------------------------------------------------
struct {
PGresult *stmt;
char name [32],
exp [512];
int argc;
Oid argv[16];
} sql_queries [KDEPO$K_SQL_MAX] = {
{NULL, "zz", "select __z$z($1::uuid, $2::integer, $3::text);", 3, {2950, 23, 25}},
...
$TRACE("PQprepare(%#0x, '%s', '%s'", conn, sql_queries[j].name, sql_queries[j].exp );
res = sql_queries[j].stmt = PQprepare(conn, sql_queries[j].name, sql_queries[j].exp,
sql_queries[j].argc, sql_queries[j].argv);
if ( (status = PQresultStatus(res)) != PGRES_COMMAND_OK )
{
$LOG(STS$K_ERROR, "PQprepare('%s') -> %d (%s), '%s'",
sql_queries[j].exp, status, PQresStatus(status), PQresultErrorMessage(res));
...
18-10-2017 18:43:11.090 25491 [DB\kdepo_db_init\157] [#0] pgconn = 0x632bf0
18-10-2017 18:43:11.090 25491 [DB\__kdepo_sql_init\106] PQprepare(0x632bf0, 'zz', 'select vcloud.__z$z($1::uuid, $2::integer, $3::text);'
18-10-2017 18:43:11.091 25491 [DB\__kdepo_sql_init\114] %VCKDEPO-E: PQprepare('select vcloud.__z$z($1::uuid, $2::integer, $3::text);') -> 7 (PGRES_FATAL_ERROR), 'ERROR: function vcloud.__z$z(uuid, integer, text) does not exist
LINE 1: select vcloud.__z$z($1::uuid, $2::integer, $3::text);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
-----------------------------------------------------------------------------------------------------------------------
No errors if p_stv and p_msg has been declared as IN. So , what I'm need to check ?
TIA.
--
С уважением,
Ruslan R. Laishev
Ruslan R. Laishev
OpenVMS bigot, natural born system/network progger, C contractor.
+79013163222
+79910009922
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.