pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedtable column vs. out param [1:0]
6+ messages / 2 participants
[nested] [flat]
* table column vs. out param [1:0]
@ 2007-08-23 05:51 Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 2 replies; 6+ messages in thread
From: Kristo Kaiv @ 2007-08-23 05:51 UTC (permalink / raw)
To: Postgresql General List <pgsql-general@postgresql.org>
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: table column vs. out param [1:0]
@ 2007-08-23 08:11 Kristo Kaiv <kristo.kaiv@skype.net>
parent: Kristo Kaiv <kristo.kaiv@skype.net>
1 sibling, 1 reply; 6+ messages in thread
From: Kristo Kaiv @ 2007-08-23 08:11 UTC (permalink / raw)
To: Kristo Kaiv <kristo.kaiv@skype.net>; +Cc: Postgresql General List <pgsql-general@postgresql.org>
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: table column vs. out param [1:0]
@ 2007-08-23 08:16 Kristo Kaiv <kristo.kaiv@skype.net>
parent: Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 0 replies; 6+ messages in thread
From: Kristo Kaiv @ 2007-08-23 08:16 UTC (permalink / raw)
To: Kristo Kaiv <kristo.kaiv@skype.net>; +Cc: Postgresql General List <pgsql-general@postgresql.org>
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] table column vs. out param [1:0]
@ 2007-08-27 07:52 Kristo Kaiv <kristo.kaiv@skype.net>
parent: Kristo Kaiv <kristo.kaiv@skype.net>
1 sibling, 1 reply; 6+ messages in thread
From: Kristo Kaiv @ 2007-08-27 07:52 UTC (permalink / raw)
To: Albe Laurenz <all@adv.magwien.gv.at>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] table column vs. out param [1:0]
@ 2007-08-27 15:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Kristo Kaiv <kristo.kaiv@skype.net>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2007-08-27 15:07 UTC (permalink / raw)
To: Kristo Kaiv <kristo.kaiv@skype.net>; +Cc: Albe Laurenz <all@adv.magwien.gv.at>; pgsql-sql
Kristo Kaiv <kristo.kaiv@skype.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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] table column vs. out param [1:0]
@ 2007-08-27 19:02 Kristo Kaiv <kristo.kaiv@skype.net>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Kristo Kaiv @ 2007-08-27 19:02 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Albe Laurenz <all@adv.magwien.gv.at>; pgsql-sql
On 27.08.2007, at 18:07, Tom Lane wrote:
> Kristo Kaiv <kristo.kaiv@skype.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)
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-08-27 19:02 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-23 05:51 table column vs. out param [1:0] Kristo Kaiv <kristo.kaiv@skype.net>
2007-08-23 08:11 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-08-23 08:16 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-08-27 07:52 ` Kristo Kaiv <kristo.kaiv@skype.net>
2007-08-27 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-27 19:02 ` Kristo Kaiv <kristo.kaiv@skype.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox