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

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