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

From: "G(dot) Allegri" <giohappy(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: column name gets substitudes inside an execute statement of a trigger function. how to avoid it?!
Date: 2009-06-09 14:52:19
Message-ID: e12429640906090752l73b23879gfe00ef22d770baf9@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brandon Metcalf 2009-06-09 14:56:04 Re: limit table to one row
Previous Message Merlin Moncure 2009-06-09 14:34:27 Re: Why lots of temp schemas are being created