[BUGS] BUG #2107: Function INOUT parameter not returned to caller, causes plpgsql malfunctions
The following bug has been logged online: Bug reference: 2107 Logged by: Tony S Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.0 Operating system: Debian 1:3.3.5-8ubuntu2, 2.6.10-5-k7, i686 Description:Function INOUT parameter not returned to caller, causes plpgsql malfunctions Details: Function defined with INOUT parameter. Value of parameter is not returned to calling function. Furthermore (which may be the same problem?), the returned variable causes plpgsql to null out calculations when it is used. (Note: I used INOUT instead of just OUT because of issues with that, which I will file separately.) Example: First function has the INOUT parameter, the remaining three are used to test it. CREATE OR REPLACE FUNCTION f_multiparam ( i1 integer, i2 varchar, INOUT o1 varchar ) AS $$ BEGIN o1 := 'i2 was ' || i2; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f_showoutparameter () RETURNS varchar AS $$ DECLARE outparameter varchar; returnvalue varchar; BEGIN returnvalue = f_multiparam(1, 'hello', outparameter); RETURN 'outparameter=' || outparameter; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f_showreturnvalue () RETURNS varchar AS $$ DECLARE outparameter varchar; returnvalue varchar; BEGIN returnvalue = f_multiparam(1, 'hello', outparameter); RETURN 'returnvalue=' || returnvalue; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f_showperformoutparameter () RETURNS varchar AS $$ DECLARE outparameter varchar; BEGIN PERFORM f_multiparam(1, 'hello', outparameter); RETURN 'outparameter=' || outparameter; END; $$ LANGUAGE plpgsql; select f_showoutparameter(), f_showreturnvalue(), f_showperformoutparameter(); Output is: CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION CREATE FUNCTION f_showoutparameter |f_showreturnvalue | f_showperformoutparameter +--+--- | returnvalue=i2 was hello | Expected Results: f_showoutparameter() Didn't really expect this to compile/work as the f_multiparam function has no return value. Anyway, it does run, and the outparameter isn't returned. Furthermore, it has corrupted the calculation in the final RETURN statement of f_showoutputparameter(). At the very least, I would have expected it to return "outparameter=". f_showreturnvalue() Didn't really expect this to compile/work as the f_multiparam function has no return value. Anyway, it does run, and the return value is what I was expecting in the out parameter. f_showperformoutparameter() This is what I expected to work, but there appears to be no out parameter. Furthermore, it has corrupted the calculation in the final RETURN statement of f_showperformoutparameter(). At the very least, I would have expected it to return "outparameter=". ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2108: Function with OUT parameters not recognized, using plpgsql
The following bug has been logged online: Bug reference: 2108 Logged by: Tony Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.0 Operating system: Debian 1:3.3.5-8ubuntu2, 2.6.10-5-k7, i686 Description:Function with OUT parameters not recognized, using plpgsql Details: Defined a function with OUT paramter. Attempts to call it fail as the function can not be found. Example: CREATE OR REPLACE FUNCTION f_multiparam ( i1 integer, i2 varchar, OUT o1 varchar ) AS $$ BEGIN o1 := 'i2 was ' || i2; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f_showperformstatus () RETURNS varchar AS $$ DECLARE outparameter varchar; BEGIN PERFORM f_multiparam(1, 'hello', outparameter); RETURN 'successfully run'; END; $$ LANGUAGE plpgsql; select f_showperformstatus(); Output: CREATE FUNCTION CREATE FUNCTION psql:bug2.sql:24: ERROR: function f_multiparam(integer, "unknown", character varying) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. CONTEXT: SQL statement "SELECT f_multiparam(1, 'hello', $1 )" PL/pgSQL function "f_showperformstatus" line 4 at perform It appears that the function is not defined properly in the system, with only 2 parameters instead of 3: \df f_multiparam List of functions Schema | Name | Result data type |Argument data types +--+---+ apps | f_multiparam | character varying | integer, character varying Explicitly casting the value 'hello' as suggested does not help. Changing the function definition from OUT to INOUT parameter is a successful workaround. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] BUG #2107: Function INOUT parameter not returned to caller, causes plpgsql malfunctions
"Tony S" <[EMAIL PROTECTED]> writes: > Function defined with INOUT parameter. Value of parameter is not returned > to calling function. You are confused about the meaning and use of INOUT. It's not some kind of pass-by-reference parameter, it's just a shorthand for separate IN and OUT parameters. In your example, the PERFORM discards the function result; the original value of 'outparameter' is not and cannot be modified by the called function. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [BUGS] BUG #2108: Function with OUT parameters not recognized, using plpgsql
"Tony" <[EMAIL PROTECTED]> writes: > Defined a function with OUT paramter. Attempts to call it fail as the > function can not be found. Apparently, you don't understand how OUT parameters work either :-( Perhaps the examples here will help: http://www.postgresql.org/docs/8.1/static/xfunc-sql.html#XFUNC-OUTPUT-PARAMETERS regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2108: Function with OUT parameters not recognized, using plpgsql
Tony wrote: > CREATE OR REPLACE FUNCTION f_multiparam ( > i1 integer, > i2 varchar, > OUT o1 varchar > ) AS > $$ > BEGIN > o1 := 'i2 was ' || i2; > END; > $$ > LANGUAGE plpgsql; > > CREATE OR REPLACE FUNCTION f_showperformstatus () RETURNS varchar AS > $$ > DECLARE > outparameter varchar; > BEGIN > PERFORM f_multiparam(1, 'hello', outparameter); > RETURN 'successfully run'; > END; > $$ > LANGUAGE plpgsql; You are misunderstanding how are functions with OUT params supposed to be called, I think. Try this: CREATE OR REPLACE FUNCTION f_multiparam ( i1 integer, i2 varchar, OUT o1 varchar ) AS $$ BEGIN o1 := 'i2 was ' || i2; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION f_showperformstatus () RETURNS varchar AS $$ DECLARE outparameter varchar; BEGIN SELECT INTO outparameter f_multiparam(1, 'hello'); RAISE NOTICE 'the out param is %', outparameter; RETURN 'successfully run'; END; $$ LANGUAGE plpgsql; The output I get is what I'd expect: alvherre=# select f_showperformstatus(); NOTICE: the out param is i2 was hello f_showperformstatus - successfully run (1 fila) I think this also applies to your INOUT report, but I haven't checked. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] PQexecParams performance
My experience using PQexecParams is that it has a performance degradation of a factor of 100’s if not thousands. I have reworked my queries to DECLARE a binary cursor for the return results and using a fixed text statement for the PQexec select. It doesn’t help on Inserts and Updates but it sure helps on straight select statement queries.
Re: [BUGS] PQexecParams performance
"Harry Rossignol" <[EMAIL PROTECTED]> writes: > My experience using PQexecParams is that it has a performance > degradation of a factor of 100's if not thousands. This is demonstrably not so in general. You may be hitting a case where the planner chooses a poor plan for lack of information about the value of a parameter ... but with no details about the query or even mention of which PG version you're talking about, this bug report isn't worth the electrons it's written on. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
