Re: table column vs. out param [1:0] - Mailing list pgsql-general

From Kristo Kaiv
Subject Re: table column vs. out param [1:0]
Date
Msg-id 215F570E-5A23-47D5-AE37-081EE755897E@skype.net
Whole thread Raw
In response to Re: table column vs. out param [1:0]  (Kristo Kaiv <kristo.kaiv@skype.net>)
List pgsql-general

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)


pgsql-general by date:

Previous
From: "Lange Marcus"
Date:
Subject: Local authentication/security
Next
From: Alban Hertroys
Date:
Subject: Re: Converting non-null unique idx to pkey