Thread: very frustrating feature-bug
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 :-)
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 SECURITYDEFINER;
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)
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 >
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
Στις 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