Обсуждение: Using the NEW record in an EXECUTE command in a PL/pgSQL trigger function
Hi all,
The SQL file joined here reproduces the simplified version of a problem that I encounter when trying to use the "NEW" record in an EXECUTE command within a PL/PgSql trigger function.
In this script the commented INSERT command works fine but the EXECUTE commande returns the following error message :
ERROR: missing FROM-clause entry for table "new"
I also tried other versions, some of them with the USING clause without success.
My aim is to write a generic trigger function ignoring the column names and types of the triggering table for partitionning purposes.
Can somebody help me ?
Thanks a lot
Best Regards
Ali
The SQL file joined here reproduces the simplified version of a problem that I encounter when trying to use the "NEW" record in an EXECUTE command within a PL/PgSql trigger function.
In this script the commented INSERT command works fine but the EXECUTE commande returns the following error message :
ERROR: missing FROM-clause entry for table "new"
I also tried other versions, some of them with the USING clause without success.
My aim is to write a generic trigger function ignoring the column names and types of the triggering table for partitionning purposes.
Can somebody help me ?
Thanks a lot
Best Regards
Ali
Вложения
Re: Using the NEW record in an EXECUTE command in a PL/pgSQL trigger function
От
Pavel Stehule
Дата:
Hello 2013/1/2 Ali Pouya <alipouya2@gmail.com>: > Hi all, > > The SQL file joined here reproduces the simplified version of a problem that > I encounter when trying to use the "NEW" record in an EXECUTE command within > a PL/PgSql trigger function. > > In this script the commented INSERT command works fine but the EXECUTE > commande returns the following error message : > > ERROR: missing FROM-clause entry for table "new" you forgot USING clause BEGIN EXECUTE 'INSERT INTO measurement1 values(new.*)' USING new; -- INSERT INTO measurement1 values(new.*); RETURN NULL; END; Regards Pavel Stehule > > I also tried other versions, some of them with the USING clause without > success. > > My aim is to write a generic trigger function ignoring the column names and > types of the triggering table for partitionning purposes. > > Can somebody help me ? > Thanks a lot > Best Regards > Ali > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
2013/1/2 Pavel Stehule <pavel.stehule@gmail.com>
Hi Pavel,
Thanks for your answer, but it did not work for me (I test on versions 9.1.6 and 9.2.1).
Fortunately I found this solution in the archives :
EXECUTE 'INSERT INTO measurement1 select $1.* ' USING new;
you forgot USING clause
BEGIN
EXECUTE 'INSERT INTO measurement1 values(new.*)' USING new;
-- INSERT INTO measurement1 values(new.*);
RETURN NULL;
END;
Regards
Pavel Stehule
Hi Pavel,
Thanks for your answer, but it did not work for me (I test on versions 9.1.6 and 9.2.1).
Fortunately I found this solution in the archives :
EXECUTE 'INSERT INTO measurement1 select $1.* ' USING new;