Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 15D399F9ABD for ; Thu, 23 Aug 2007 05:12:08 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 49240-06 for ; Thu, 23 Aug 2007 05:12:05 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from smtp-out.neti.ee (barracuda.neti.ee [194.126.101.113]) by postgresql.org (Postfix) with ESMTP id B99839FA198 for ; Thu, 23 Aug 2007 05:12:04 -0300 (ADT) Received: from localhost (localhost [127.0.0.1]) by MXR-4.estpak.ee (Postfix) with ESMTP id 337158F49; Thu, 23 Aug 2007 11:12:01 +0300 (EEST) X-Virus-Scanned: amavisd-new at !change-mydomain-variable!.example.com Received: from smtp-out.neti.ee ([127.0.0.1]) by localhost (MXR-1.estpak.ee [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id djCak1UHHDX1; Thu, 23 Aug 2007 11:11:59 +0300 (EEST) Received: from Relayhost3.neti.ee (unknown [88.196.174.169]) by MXR-4.estpak.ee (Postfix) with ESMTP id 2898D8C5C; Thu, 23 Aug 2007 11:11:59 +0300 (EEST) Received: from [172.16.27.126] (unknown [194.126.108.2]) by Relayhost3.neti.ee (Postfix) with ESMTP id 277FA9BF8B; Thu, 23 Aug 2007 11:11:58 +0300 (EEST) In-Reply-To: <5177DE35-7DAD-4DAF-B314-242DAA2B15D1@skype.net> References: <5177DE35-7DAD-4DAF-B314-242DAA2B15D1@skype.net> Mime-Version: 1.0 (Apple Message framework v752.3) Content-Type: multipart/alternative; boundary=Apple-Mail-5--903879428 Message-Id: <48E7152F-8921-431A-86CF-D03032EAC940@skype.net> Cc: Postgresql General List From: Kristo Kaiv Subject: Re: table column vs. out param [1:0] Date: Thu, 23 Aug 2007 11:11:57 +0300 To: Kristo Kaiv X-Mailer: Apple Mail (2.752.3) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/1364 X-Sequence-Number: 119213 --Apple-Mail-5--903879428 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed 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) --Apple-Mail-5--903879428 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=ISO-8859-1
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.=A0
The problem now=A0is 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.=A0

snippet from = code
-[cut]--

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

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

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

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

 =

= --Apple-Mail-5--903879428--