Re: [SQL] very frustrating feature-bug
On 2010-02-17, silly sad 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; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] very frustrating feature-bug
On 02/17/10 13:51, Jasen Betts wrote: On 2010-02-17, silly sad 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) -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] very frustrating feature-bug
2010/2/17 silly sad : > On 02/17/10 13:51, Jasen Betts wrote: >> >> On 2010-02-17, silly sad 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Referencing external table in update/insert triggers
Hi I have the following table structure for which I am trying to set a AFTER INSERT or UPDATE trigger: CREATE OR REPLACE FUNCTION update_data() RETURNS TRIGGER AS $update_data$ BEGIN IF NEW.t1f4 > t2.t2f4 UPDATE t2 set t2f2=NEW.t1f2, t2f3=NEW.t1f3, t2f4=NEW.t1f4 where t2f1=NEW.d1; RETURN NEW; END IF; END; $update_data$ LANGUAGE plpgsql; [t1] t1f1 integer t1f2 integer t1f3 integer t1f4 timestamp without time zone t1f5 character varying t1f6 real d1 bigint [fk: t2->t2f1] CREATE TRIGGER test_trigger AFTER INSERT OR UPDATE ON t1 FOR EACH ROW EXECUTE PROCEDURE update_data(); [t2] t2f1 integer NOT NULL t2f2 integer t2f3 integer t2f4 timestamp without time zone I would like to compare the date present in the t2f4 with the new data being updated through the trigger. Using the if line as listed above returns an error. Is it possible to reference table t2 within the same trigger? I could also use another trigger (BEFORE UPDATE) on t2 to achieve the same, but would like to avoid it. With regards Amitabh
Re: [SQL] very frustrating feature-bug
silly sad 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] very frustrating feature-bug
Στις Wednesday 17 February 2010 17:27:11 ο/η Tom Lane έγραψε: > silly sad 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, postg...@smadevnew:~> postg...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
