Re: [SQL] Issue with a variable in a function
Thank you for your reply.
I've tried that and the function runs OK, my issue then is maybe selecting the
function? I've tried both select ppr_pf_inn_antall(2011,1,52,[8,3]) and select
ppr_pf_inn_antall(2011,1,52,{8,3}) but none of them runs. I've read up about
arrays and functions, but guess I'm missing the "functions for dummies"
tutorial. :)
Please note that since posting this I've have changed some details such as
naming etc.
From: David Johnston [via PostgreSQL]
[mailto:ml-node+s1045698n4975030...@n5.nabble.com]
Sent: 8. november 2011 18:28
To: Lund, Thomas
Subject: Re: Issue with a variable in a function
-Original Message-
From: [hidden email]
[mailto:[hidden email]]
On Behalf Of tlund79
Sent: Tuesday, November 08, 2011 8:17 AM
To: [hidden email]
Subject: [SQL] Issue with a variable in a function
The issue relates to the variable "prosjektkode" ($4).
CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int,
prosjektkode int) RETURNS int AS $$ DECLARE antall bigint;
---/Original Message--
Read about "ARRAY"s
Change your function signature to something like:
CREATE OR REPLACE FUNCTION ppr_data(aarstall int, frauke int, tiluke int,
prosjektkode int[] ) -- Note the change to int[] from int for prosjektkode
David J.
--
Sent via pgsql-sql mailing list ([hidden
email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
If you reply to this email, your message will be added to the discussion below:
http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4975030.html
To unsubscribe from Issue with a variable in a function, click
here.
See how NAML generates this
email
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4977097.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Re: [SQL] Issue with a variable in a function
I solved this one by trial and error. You were right I needed brackets to indicate an array, but also needed to replace "in ($4)" with "= any ($4)" -- View this message in context: http://postgresql.1045698.n5.nabble.com/Issue-with-a-variable-in-a-function-tp4974235p4977361.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- 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] Partitionning + Trigger and Execute not working as expected
Thank you for the help. But it doesn't work : EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); QUERY: INSERT INTO job_2011_11 values (117916386,-5,,2,2,11,1,,00,"2011-11-07 00:00:00","2011-11-07 00:00:00",,0,0,,0) CONTEXT: PL/pgSQL function "job_insert_trigger" line 9 at instruction EXECUTE It looks like the NULL values are blanks in the generated query. Using the query below produces an error too EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)'; ERREUR: missing FROM clause for table « new » SQL :42P01 QUERY: INSERT INTO job_2011_11 values (NEW.*) CONTEXT: PL/pgSQL function "job_insert_trigger" line 9 at instruction EXECUTE Even if the query below is fine (the exact content I try to build as a String to use with EXECUTE) INSERT INTO job_2011_11 values (NEW.*) Is there a way to solve this? Isn't it a bug (in how EXECUTE works)? On Wed, Nov 9, 2011 at 1:35 AM, Josh Kupershmidt wrote: > On Tue, Nov 8, 2011 at 11:04 AM, Sylvain Mougenot > wrote: > > EXECUTE 'INSERT INTO '|| currentTableName || ' values (NEW.*)'; > > The quotes in the above line are wrong; you want it like: > > EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); > > Josh > -- Sylvain Mougenot
Re: [SQL] Partitionning + Trigger and Execute not working as expected
On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot wrote: > Even if the query below is fine (the exact content I try to build as a > String to use with EXECUTE) > INSERT INTO job_2011_11 values (NEW.*) > Is there a way to solve this? > Isn't it a bug (in how EXECUTE works)? I doubt this is a bug in EXECUTE; if you think it is, try to post a self-contained test case. For example, this similar example works fine: CREATE TABLE foo (a int, b int); CREATE TABLE job_2011_11 (c int, d int); CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE currentTableName character varying := 'job_' || '2011_11'; BEGIN EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); INSERT INTO foo (a, b) VALUES (1, 2); Josh -- 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] Partitionning + Trigger and Execute not working as expected
As I mentioned before, your code works on special cases (insert with all the columns) and those are very few cases. Try this CREATE TABLE foo (a int, b int); CREATE TABLE job_2011_11 (c int, d int); CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE currentTableName character varying := 'job_' || '2011_11'; BEGIN EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); INSERT INTO foo (a, b) VALUES (1, 2); INSERT INTO foo (a) VALUES (10); ERROR: LINE 1: INSERT INTO job_2011_11 values (10,) ^ QUERY: INSERT INTO job_2011_11 values (10,) CONTEXT: PL/pgSQL function "job_insert_trigger" line 5 at instruction EXECUTE Regarding the self contained test for EXECUTE it's the same code. In the trigger the use of this code doesn't work : EXECUTE '*INSERT INTO job_2011_11 values (NEW.*)*'; but this one does work *INSERT INTO job_2011_11 values (NEW.*)*; So it looks like a trouble with EXECUTE to me! On Wed, Nov 9, 2011 at 8:25 PM, Josh Kupershmidt wrote: > On Wed, Nov 9, 2011 at 6:57 AM, Sylvain Mougenot > wrote: > > > Even if the query below is fine (the exact content I try to build as a > > String to use with EXECUTE) > > INSERT INTO job_2011_11 values (NEW.*) > > Is there a way to solve this? > > Isn't it a bug (in how EXECUTE works)? > > I doubt this is a bug in EXECUTE; if you think it is, try to post a > self-contained test case. For example, this similar example works > fine: > > > CREATE TABLE foo (a int, b int); > CREATE TABLE job_2011_11 (c int, d int); > > CREATE OR REPLACE FUNCTION job_insert_trigger() > RETURNS TRIGGER AS > $BODY$ > DECLARE >currentTableName character varying := 'job_' || '2011_11'; > BEGIN > EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); > RETURN NULL; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > > CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo > FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); > INSERT INTO foo (a, b) VALUES (1, 2); > > > Josh > -- Sylvain Mougenot
Re: [SQL] Partitionning + Trigger and Execute not working as expected
On Wed, Nov 9, 2011 at 4:39 PM, Sylvain Mougenot wrote: > As I mentioned before, your code works on special cases (insert with all the > columns) and those are very few cases. > Try this > CREATE TABLE foo (a int, b int); > CREATE TABLE job_2011_11 (c int, d int); > > CREATE OR REPLACE FUNCTION job_insert_trigger() > RETURNS TRIGGER AS > $BODY$ > DECLARE > currentTableName character varying := 'job_' || '2011_11'; > BEGIN > EXECUTE 'INSERT INTO '|| currentTableName || ' values ' || (NEW.*); > RETURN NULL; > END; > $BODY$ > LANGUAGE plpgsql VOLATILE > COST 100; > > CREATE TRIGGER job_insert_trg BEFORE INSERT ON foo > FOR EACH ROW EXECUTE PROCEDURE job_insert_trigger(); > INSERT INTO foo (a, b) VALUES (1, 2); > INSERT INTO foo (a) VALUES (10); > ERROR: > LINE 1: INSERT INTO job_2011_11 values (10,) > ^ > QUERY: INSERT INTO job_2011_11 values (10,) > CONTEXT: PL/pgSQL function "job_insert_trigger" line 5 at instruction > EXECUTE Oh, I see what you're on about now. Sounds like you're looking for the USING clause of EXECUTE. Try this: CREATE OR REPLACE FUNCTION job_insert_trigger() RETURNS TRIGGER AS $BODY$ DECLARE currentTableName character varying := 'job_' || '2011_11'; BEGIN EXECUTE 'INSERT INTO '|| currentTableName || ' (c, d) VALUES ($1, $2)' USING NEW.a, NEW.b; RETURN NULL; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; Josh -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
