pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedvery frustrating feature-bug
6+ messages / 5 participants
[nested] [flat]
* very frustrating feature-bug
@ 2010-02-17 07:20 silly sad <sad@bankir.ru>
0 siblings, 1 reply; 6+ messages in thread
From: silly sad @ 2010-02-17 07:20 UTC (permalink / raw)
To: pgsql-sql
acc=>
CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
RETURNS usr AS $$
INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
RETURNING usr.*;
$$ LANGUAGE sql SECURITY DEFINER;
acc=>
ERROR: return type mismatch in function declared to return usr
DETAIL: Function's final statement must be a SELECT.
CONTEXT: SQL function "add_user"
SURPRISE :-) SURPRISE :-)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: very frustrating feature-bug
@ 2010-02-17 10:51 Jasen Betts <jasen@xnet.co.nz>
parent: silly sad <sad@bankir.ru>
0 siblings, 1 reply; 6+ messages in thread
From: Jasen Betts @ 2010-02-17 10:51 UTC (permalink / raw)
To: pgsql-sql
On 2010-02-17, silly sad <sad@bankir.ru> wrote:
>
> acc=>
>
> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
> RETURNS usr AS $$
> INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
> RETURNING usr.*;
> $$ LANGUAGE sql SECURITY DEFINER;
>
> acc=>
>
> ERROR: return type mismatch in function declared to return usr
> DETAIL: Function's final statement must be a SELECT.
> CONTEXT: SQL function "add_user"
>
> SURPRISE :-) SURPRISE :-)
SQL functions are inlined when invoked, and so must be valid subselects.
rewrite it in plpgsql.
CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
RETURNS usr AS $$
DECLARE
retval usr;
BEGIN
INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
RETURNING usr.* INTO retval;
RETURN retval;
END;
$$ LANGUAGE PLPGSQL SECURITY DEFINER;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: very frustrating feature-bug
@ 2010-02-17 11:34 silly sad <sad@bankir.ru>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 6+ messages in thread
From: silly sad @ 2010-02-17 11:34 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
On 02/17/10 13:51, Jasen Betts wrote:
> On 2010-02-17, silly sad<sad@bankir.ru> wrote:
>>
>> acc=>
>>
>> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
>> RETURNS usr AS $$
>> INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
>> RETURNING usr.*;
>> $$ LANGUAGE sql SECURITY DEFINER;
>>
>> acc=>
>>
>> ERROR: return type mismatch in function declared to return usr
>> DETAIL: Function's final statement must be a SELECT.
>> CONTEXT: SQL function "add_user"
>>
>> SURPRISE :-) SURPRISE :-)
>
> SQL functions are inlined when invoked, and so must be valid subselects.
>
> rewrite it in plpgsql.
thanx for advice.
may i ask? when this feature will be fixed?
(now i am using 8.3.9)
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: very frustrating feature-bug
@ 2010-02-17 11:37 Pavel Stehule <pavel.stehule@gmail.com>
parent: silly sad <sad@bankir.ru>
1 sibling, 0 replies; 6+ messages in thread
From: Pavel Stehule @ 2010-02-17 11:37 UTC (permalink / raw)
To: silly sad <sad@bankir.ru>; +Cc: pgsql-sql
2010/2/17 silly sad <sad@bankir.ru>:
> On 02/17/10 13:51, Jasen Betts wrote:
>>
>> On 2010-02-17, silly sad<sad@bankir.ru> wrote:
>>>
>>> acc=>
>>>
>>> CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
>>> RETURNS usr AS $$
>>> INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
>>> RETURNING usr.*;
>>> $$ LANGUAGE sql SECURITY DEFINER;
>>>
>>> acc=>
>>>
>>> ERROR: return type mismatch in function declared to return usr
>>> DETAIL: Function's final statement must be a SELECT.
>>> CONTEXT: SQL function "add_user"
>>>
>>> SURPRISE :-) SURPRISE :-)
>>
>> SQL functions are inlined when invoked, and so must be valid subselects.
>>
>> rewrite it in plpgsql.
>
> thanx for advice.
>
> may i ask? when this feature will be fixed?
> (now i am using 8.3.9)
>
please, report it as bug to pgsql-bugs
Regards
Pavel Stehule
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: very frustrating feature-bug
@ 2010-02-17 15:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: silly sad <sad@bankir.ru>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2010-02-17 15:27 UTC (permalink / raw)
To: silly sad <sad@bankir.ru>; +Cc: pgsql-sql
silly sad <sad@bankir.ru> writes:
> may i ask? when this feature will be fixed?
> (now i am using 8.3.9)
The example works fine for me in 8.4.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: very frustrating feature-bug
@ 2010-02-17 15:33 Achilleas Mantzios <achill@matrix.gatewaynet.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Achilleas Mantzios @ 2010-02-17 15:33 UTC (permalink / raw)
To: pgsql-sql; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; silly sad <sad@bankir.ru>
Óôéò Wednesday 17 February 2010 17:27:11 ï/ç Tom Lane Ýãñáøå:
> silly sad <sad@bankir.ru> writes:
> > may i ask? when this feature will be fixed?
> > (now i am using 8.3.9)
>
> The example works fine for me in 8.4.
Same for me,
postgres@smadevnew:~>
postgres@smadevnew:~> psql
psql (8.4.1)
Type "help" for help.
dynacom=# CREATE OR REPLACE FUNCTION add_user (TEXT, TEXT, TEXT, TEXT)
dynacom-# RETURNS usr AS $$
dynacom$# INSERT INTO usr (login,pass,name,email) VALUES ($1,$2,$3,$4)
dynacom$# RETURNING usr.*;
dynacom$# $$ LANGUAGE sql SECURITY DEFINER;
CREATE FUNCTION
dynacom=#
dynacom=#
>
> regards, tom lane
>
--
Achilleas Mantzios
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-02-17 15:33 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-02-17 07:20 very frustrating feature-bug silly sad <sad@bankir.ru>
2010-02-17 10:51 ` Jasen Betts <jasen@xnet.co.nz>
2010-02-17 11:34 ` silly sad <sad@bankir.ru>
2010-02-17 11:37 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-02-17 15:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-02-17 15:33 ` Achilleas Mantzios <achill@matrix.gatewaynet.com>
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