Re: column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!

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();
>