Thread: column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
From
"G. Allegri"
Date:
Hello list. I'm a newbie with plpgsql, so I'm sorry for doing stupid questions... I have a situation whit one table where items are related to two other tables through a common id (unique in the first table) and the table name. Whenever the user execute an operation on an item of the first one ("prima"), the related items in tables "seconda" or "terza" must be updated. CREATE TABLE prima ( id serial NOT NULL, nome character varying(100), table character varying(10), ## this contains "seconda" or "terza" CONSTRAINT prima_pkey PRIMARY KEY (id) ) CREATE TABLE seconda ( id serial NOT NULL, nome character varying(100), CONSTRAINT seconda_pkey PRIMARY KEY (id) ) CREATE TABLE seconda ( id serial NOT NULL, nome character varying(100), CONSTRAINT seconda_pkey PRIMARY KEY (id) ) So I need to retrieve the table name dynamically inside the function, and AFAIK I can do it only using an execute statement. BUT when I do something like "INSERT INTO prima (nome,table) VALUES ('lets_try','seconda')" I get the following error: ERROR: Column 'lets_try' does not exist LINE 1: INSERT INTO seconda (nome) VALUES ('lets_try') My function and trigger are: CREATE OR REPLACE FUNCTION fun1() RETURNS TRIGGER AS $primaprova$ DECLARE nome varchar; BEGIN IF (TG_OP='INSERT') THEN execute 'INSERT INTO ' || NEW.table || ' (nome) VALUES( "' || NEW.nome || '" );'; RETURN NEW; ELSEIF (TG_OP='UPDATE') THEN execute 'UPDATE '|| NEW.table ||' SET nome='|| NEW.nome ||';'; RETURN NEW; ELSEIF (TG_OP='DELETE') THEN execute 'DELETE FROM '|| OLD.table ||' where nome='|| OLD.nome ||';'; RETURN OLD; END IF; END; $primaprova$ LANGUAGE plpgsql; CREATE TRIGGER tr1 AFTER INSERT OR UPDATE OR DELETE ON prima FOR EACH ROW EXECUTE PROCEDURE fun1(); It seems like the string 'nome' inside the execute statement gets substituded by the NEW.nome value, while it shouldn't. How can I solve this? Where am I wrong? Thanks a lot, Gioanni PS: I'm using PG 1.8.4 on Windows XP
Re: column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
From
Richard Huxton
Date:
G. Allegri wrote: > > ERROR: Column 'lets_try' does not exist > LINE 1: INSERT INTO seconda (nome) VALUES ('lets_try') > > My function and trigger are: > > CREATE OR REPLACE FUNCTION fun1() RETURNS TRIGGER AS > $primaprova$ > DECLARE > nome varchar; > BEGIN > IF (TG_OP='INSERT') THEN > execute 'INSERT INTO ' || NEW.table || ' (nome) VALUES( "' || NEW.nome > || '" );'; This will give VALUES ("lets_try") which is not a valid way to quote a string. It *is* a valid way to quote an identifier (e.g. a column). You probably want to use the quote_literal() function anyway to escape any single-quotes in the value being quoted. -- Richard Huxton Archonet Ltd
Re: column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
From
"G. Allegri"
Date:
Great, quote_literal() solved it! It was so easy :) Thx Richard 2009/6/9 Richard Huxton <dev@archonet.com>: > G. Allegri wrote: >> >> ERROR: Column 'lets_try' does not exist >> LINE 1: INSERT INTO seconda (nome) VALUES ('lets_try') >> >> My function and trigger are: >> >> CREATE OR REPLACE FUNCTION fun1() RETURNS TRIGGER AS >> $primaprova$ >> DECLARE >> nome varchar; >> BEGIN >> IF (TG_OP='INSERT') THEN >> execute 'INSERT INTO ' || NEW.table || ' (nome) VALUES( "' || NEW.nome >> || '" );'; > > This will give VALUES ("lets_try") which is not a valid way to quote a > string. It *is* a valid way to quote an identifier (e.g. a column). > > You probably want to use the quote_literal() function anyway to escape any > single-quotes in the value being quoted. > > -- > Richard Huxton > Archonet Ltd >
Re: column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
From
"G. Allegri"
Date:
Stephan, you're rigth. It was just a first try. In the real situation "seconda" and "terza" have a prima_id column. So the right one is: execute 'UPDATE '|| NEW.tabella ||' SET nome='|| quote_literal(NEW.nome) ||' WEHERE prima_id=' || NEW.id || ';'; I though that double apices would quote. That was the error. quote_literal() solves it. 2009/6/9 Stephan Szabo <sszabo@megazone.bigpanda.com>: > On Tue, 9 Jun 2009, G. Allegri wrote: > >> Hello list. >> I'm a newbie with plpgsql, so I'm sorry for doing stupid questions... >> I have a situation whit one table where items are related to two other >> tables through a common id (unique in the first table) and the table >> name. Whenever the user execute an operation on an item of the first >> one ("prima"), the related items in tables "seconda" or "terza" must >> be updated. >> >> CREATE TABLE prima >> ( >> id serial NOT NULL, >> nome character varying(100), >> table character varying(10), ## this contains "seconda" or "terza" >> CONSTRAINT prima_pkey PRIMARY KEY (id) >> ) >> >> CREATE TABLE seconda >> ( >> id serial NOT NULL, >> nome character varying(100), >> CONSTRAINT seconda_pkey PRIMARY KEY (id) >> ) >> >> CREATE TABLE seconda >> ( >> id serial NOT NULL, >> nome character varying(100), >> CONSTRAINT seconda_pkey PRIMARY KEY (id) >> ) >> >> >> So I need to retrieve the table name dynamically inside the function, >> and AFAIK I can do it only using an execute statement. BUT when I do >> something like "INSERT INTO prima (nome,table) VALUES >> ('lets_try','seconda')" I get the following error: >> >> ERROR: Column 'lets_try' does not exist >> LINE 1: INSERT INTO seconda (nome) VALUES ('lets_try') > > Are you sure that's the error message (specifically the context)? > Specifically, the query below in the function looks like it would > generate: > > INSERT INTO seconda (name) VALUES ("lets_try") > > which means use lets_try as a quoted column name, as opposed to > ('lets_try') which means the string literal. > > In addition, what are the semantics of update supposed to be? It looks > like if you update a row in prima, it's going to set all the name fields > to the new name? Is that intentional, or was the intent to change only the > row with the old name? > >> CREATE OR REPLACE FUNCTION fun1() RETURNS TRIGGER AS >> $primaprova$ >> DECLARE >> nome varchar; >> BEGIN >> IF (TG_OP='INSERT') THEN >> execute 'INSERT INTO ' || NEW.table || ' (nome) VALUES( "' || NEW.nome >> || '" );'; >> RETURN NEW; >> ELSEIF (TG_OP='UPDATE') THEN >> execute 'UPDATE '|| NEW.table ||' SET nome='|| NEW.nome ||';'; >> RETURN NEW; >> ELSEIF (TG_OP='DELETE') THEN >> execute 'DELETE FROM '|| OLD.table ||' where nome='|| OLD.nome ||';'; >> RETURN OLD; >> END IF; >> END; >> $primaprova$ LANGUAGE plpgsql; >> CREATE TRIGGER tr1 AFTER INSERT OR UPDATE OR DELETE ON prima FOR EACH >> ROW EXECUTE PROCEDURE fun1(); >
Re: column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
From
Stephan Szabo
Date:
On Tue, 9 Jun 2009, G. Allegri wrote: > Hello list. > I'm a newbie with plpgsql, so I'm sorry for doing stupid questions... > I have a situation whit one table where items are related to two other > tables through a common id (unique in the first table) and the table > name. Whenever the user execute an operation on an item of the first > one ("prima"), the related items in tables "seconda" or "terza" must > be updated. > > CREATE TABLE prima > ( > id serial NOT NULL, > nome character varying(100), > table character varying(10), ## this contains "seconda" or "terza" > CONSTRAINT prima_pkey PRIMARY KEY (id) > ) > > CREATE TABLE seconda > ( > id serial NOT NULL, > nome character varying(100), > CONSTRAINT seconda_pkey PRIMARY KEY (id) > ) > > CREATE TABLE seconda > ( > id serial NOT NULL, > nome character varying(100), > CONSTRAINT seconda_pkey PRIMARY KEY (id) > ) > > > So I need to retrieve the table name dynamically inside the function, > and AFAIK I can do it only using an execute statement. BUT when I do > something like "INSERT INTO prima (nome,table) VALUES > ('lets_try','seconda')" I get the following error: > > ERROR: Column 'lets_try' does not exist > LINE 1: INSERT INTO seconda (nome) VALUES ('lets_try') Are you sure that's the error message (specifically the context)? Specifically, the query below in the function looks like it would generate: INSERT INTO seconda (name) VALUES ("lets_try") which means use lets_try as a quoted column name, as opposed to ('lets_try') which means the string literal. In addition, what are the semantics of update supposed to be? It looks like if you update a row in prima, it's going to set all the name fields to the new name? Is that intentional, or was the intent to change only the row with the old name? > CREATE OR REPLACE FUNCTION fun1() RETURNS TRIGGER AS > $primaprova$ > DECLARE > nome varchar; > BEGIN > IF (TG_OP='INSERT') THEN > execute 'INSERT INTO ' || NEW.table || ' (nome) VALUES( "' || NEW.nome > || '" );'; > RETURN NEW; > ELSEIF (TG_OP='UPDATE') THEN > execute 'UPDATE '|| NEW.table ||' SET nome='|| NEW.nome ||';'; > RETURN NEW; > ELSEIF (TG_OP='DELETE') THEN > execute 'DELETE FROM '|| OLD.table ||' where nome='|| OLD.nome ||';'; > RETURN OLD; > END IF; > END; > $primaprova$ LANGUAGE plpgsql; > CREATE TRIGGER tr1 AFTER INSERT OR UPDATE OR DELETE ON prima FOR EACH > ROW EXECUTE PROCEDURE fun1();