Re: [GENERAL] table column vs. out param [1:0]

I am trying to implement the new out parameters in functions and
stumbled upon a problem.
There is an internal requirement for our databases that every
function call always returns 2 params status & status_text.
The problem now is that plpgsql selects the out params themselves
into out params instead of function call results that i need there.
If this is the expected behavior of out params it makes using out
params a bit complicated if some table attributes
happen to have the same name as out params. How can i overcome this
situation? I can understand function variables having precedence
over column names as you can freely rename them but out params is a
different situation.

snippet from code
-[cut]--

out status int, -- 200
out status_text text -- OK
) AS $$
BEGIN

-[cut]--
SELECT status, status_text
FROM service._simple_add(
i_key_user
,i_key_service
,i_action
,i_subscr_len)
INTO status, status_text;
-[cut]-

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)

On 23.08.2007, at 8:51, Kristo Kaiv wrote:

> I am trying to implement the new out parameters in functions and
> stumbled upon a problem.
> There is an internal requirement for our databases that every
> function call always returns 2 params status & status_text.
> The problem now is that plpgsql selects the out params themselves
> into out params instead of function call results that i need there.
> If this is the expected behavior of out params it makes using out
> params a bit complicated if some table attributes
> happen to have the same name as out params. How can i overcome this
> situation? I can understand function variables having precedence
> over column names as you can freely rename them but out params is a
> different situation.
>
> snippet from code
> -[cut]--
>
> out status int, -- 200
> out status_text text -- OK
> ) AS $$
> BEGIN
>
> -[cut]--
> SELECT status, status_text
> FROM service._simple_add(
> i_key_user
> ,i_key_service
> ,i_action
> ,i_subscr_len)
> INTO status, status_text;
> -[cut]-

using a table (function) alias seems to solve the problem.

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)

On 23.08.2007, at 11:11, Kristo Kaiv wrote:

>
> On 23.08.2007, at 8:51, Kristo Kaiv wrote:
>
>> I am trying to implement the new out parameters in functions and
>> stumbled upon a problem.
>> There is an internal requirement for our databases that every
>> function call always returns 2 params status & status_text.
>> The problem now is that plpgsql selects the out params themselves
>> into out params instead of function call results that i need there.
>> If this is the expected behavior of out params it makes using out
>> params a bit complicated if some table attributes
>> happen to have the same name as out params. How can i overcome
>> this situation? I can understand function variables having precedence
>> over column names as you can freely rename them but out params is
>> a different situation.
>>
>> snippet from code
>> -[cut]--
>>
>> out status int, -- 200
>> out status_text text -- OK
>> ) AS $$
>> BEGIN
>>
>> -[cut]--
>> SELECT status, status_text
>> FROM service._simple_add(
>> i_key_user
>> ,i_key_service
>> ,i_action
>> ,i_subscr_len)
>> INTO status, status_text;
>> -[cut]-
>
> using a table (function) alias seems to solve the problem.
>
then again select "status", "status_text" takes the variable again?
why is that?
this kind of behaviour seems kind of bizarre to me.

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)

On 23.08.2007, at 11:55, Albe Laurenz wrote:
> CREATE FUNCTION b(status OUT integer, status_text OUT text)
> LANGUAGE plpgsql STABLE STRICT AS
> $$DECLARE
> RENAME status TO out_status;
> RENAME status_text TO out_status_text;
> BEGIN
> SELECT status, status_text
> FROM a()
> INTO out_status, out_status_text;
> RETURN;
> END;$$;
>
> See
> http://www.postgresql.org/docs/current/static/plpgsql-
> declarations.html#
> PLPGSQL-DECLARATION-RENAMING-VARS
>
> Yours,
> Laurenz Albe
From http://www.postgresql.org/docs/current/static/plpgsql-
declarations.html#PLPGSQL-DECLARATION-RENAMING-VARS
Note: RENAME appears to be broken as of PostgreSQL 7.3. Fixing this
is of low priority, since ALIAS covers most of the practical uses of
RENAME.
Seems to work though. Could somebody please confirm/reject that this
has been fixed?

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)

Kristo Kaiv <kristo(dot)kaiv(at)skype(dot)net> writes:
> From http://www.postgresql.org/docs/current/static/plpgsql-
> declarations.html#PLPGSQL-DECLARATION-RENAMING-VARS
> Note: RENAME appears to be broken as of PostgreSQL 7.3. Fixing this
> is of low priority, since ALIAS covers most of the practical uses of
> RENAME.
> Seems to work though. Could somebody please confirm/reject that this
> has been fixed?

It "works" only for very small values of "works". See the links in
the TODO item for it.

regards, tom lane

On 27.08.2007, at 18:07, Tom Lane wrote:

> Kristo Kaiv <kristo(dot)kaiv(at)skype(dot)net> writes:
>> From http://www.postgresql.org/docs/current/static/plpgsql-
>> declarations.html#PLPGSQL-DECLARATION-RENAMING-VARS
>> Note: RENAME appears to be broken as of PostgreSQL 7.3. Fixing this
>> is of low priority, since ALIAS covers most of the practical uses of
>> RENAME.
>> Seems to work though. Could somebody please confirm/reject that this
>> has been fixed?
>
> It "works" only for very small values of "works". See the links in
> the TODO item for it.
>
> regards, tom lane
Tom, the TODO item says:
Server-Side Languages
PL/pgSQL
Fix RENAME to work on variables other than OLD/NEW

but it seems to already work. I have tested it on 8.2 and have not
noticed any problems.

orderdb_test=# \df+ test
List of functions
-[ RECORD 1 ]-------+--------------------------
Schema | public
Name | test
Result data type | text
Argument data types | i_a text, OUT asi text
Owner | kristok
Language | plpgsql
Source code |
: DECLARE
: -- o_asi ALIAS FOR $2;
: RENAME asi TO o_asi;
: BEGIN
: select 32 into o_asi;
: return;
: END;
:
Description |

orderdb_test=# select * from test(123);
asi
-----
32
(1 row)

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)