Thread: How can I get one OLD.* field in a dynamic query inside a trigger function ?
How can I get one OLD.* field in a dynamic query inside a trigger function ?
From
Bruno Baguette
Date:
Hello ! I'm trying to write a little trigger function with variable arguments quantity (at least one, but can be 2,3,4 arguments). Theses arguments are fields name, so only varchar variable. Since it is a dynamic query, I use the EXECUTE statement as explained on <http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN> CREATE OR REPLACE FUNCTION delete_acl_trigger() RETURNS trigger AS $delete_acl_trigger$ DECLARE BEGIN FOR i IN 0 .. TG_NARGS LOOP EXECUTE 'SELECT delete_acl(OLD.' || TG_ARGV[i] || ');'; END LOOP; RETURN OLD; END; $delete_acl_trigger$ LANGUAGE plpgsql; But, when the trigger is triggered, I receive this error message : "Query failed: ERROR: OLD used in query that is not in a rule" How can I get the value of the OLD.' || TG_ARGV[i] field ? Many thanks in advance ! Kind regards, -- Bruno Baguette - bruno.baguette@gmail.com
Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?
From
Pavel Stehule
Date:
2009/10/24 Bruno Baguette <bruno.baguette@gmail.com>: > Hello ! > > I'm trying to write a little trigger function with variable arguments > quantity (at least one, but can be 2,3,4 arguments). > Theses arguments are fields name, so only varchar variable. > > Since it is a dynamic query, I use the EXECUTE statement as explained on > <http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN> > > CREATE OR REPLACE FUNCTION delete_acl_trigger() RETURNS trigger AS > $delete_acl_trigger$ > DECLARE > BEGIN > FOR i IN 0 .. TG_NARGS LOOP > EXECUTE 'SELECT delete_acl(OLD.' || TG_ARGV[i] || ');'; > END LOOP; > RETURN OLD; > END; > $delete_acl_trigger$ LANGUAGE plpgsql; > > But, when the trigger is triggered, I receive this error message : > "Query failed: ERROR: OLD used in query that is not in a rule" > > How can I get the value of the OLD.' || TG_ARGV[i] field ? OLD is variable only in PLpgSQL procedure, - outside doesn't exists. If you have a 8.4, you can use USING clause EXPLAIN 'SELECT $1.' || TG_ARGV[i] INTO somevar USING OLD; http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards Pavel Stehule > > Many thanks in advance ! > > Kind regards, > > -- > Bruno Baguette - bruno.baguette@gmail.com > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?
From
Bruno Baguette
Date:
Le 24/10/09 06:46, Pavel Stehule a écrit : > 2009/10/24 Bruno Baguette <bruno.baguette@gmail.com>: >> Hello ! >> >> I'm trying to write a little trigger function with variable arguments >> quantity (at least one, but can be 2,3,4 arguments). >> Theses arguments are fields name, so only varchar variable. >> >> Since it is a dynamic query, I use the EXECUTE statement as explained on >> <http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN> >> >> CREATE OR REPLACE FUNCTION delete_acl_trigger() RETURNS trigger AS >> $delete_acl_trigger$ >> DECLARE >> BEGIN >> FOR i IN 0 .. TG_NARGS LOOP >> EXECUTE 'SELECT delete_acl(OLD.' || TG_ARGV[i] || ');'; >> END LOOP; >> RETURN OLD; >> END; >> $delete_acl_trigger$ LANGUAGE plpgsql; >> >> But, when the trigger is triggered, I receive this error message : >> "Query failed: ERROR: OLD used in query that is not in a rule" >> >> How can I get the value of the OLD.' || TG_ARGV[i] field ? > > OLD is variable only in PLpgSQL procedure, - outside doesn't exists. > If you have a 8.4, you can use USING clause > > EXPLAIN 'SELECT $1.' || TG_ARGV[i] INTO somevar USING OLD; > > http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Hello Pavel, Thanks for your answer ! Unfortunately, I'm running PostgreSQL 8.3.7 (PostgreSQL 8.3.7 on i586-mandriva-linux-gnu, compiled by GCC i586-mandriva-linux-gnu-gcc (GCC) 4.2.3 (4.2.3-6mnb1)). Since 8.4.1 is not available for Mandriva 2009.1, I can only have this PostgreSQL version. (I don't have root access on that server). Is there another way, usable in PostgreSQL 8.3.7, to solve my problem ? Many thanks in advance ! Kind Regards, -- Bruno Baguette - bruno.baguette@gmail.com
Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?
From
Pavel Stehule
Date:
2009/10/24 Bruno Baguette <bruno.baguette@gmail.com>: > Le 24/10/09 06:46, Pavel Stehule a écrit : >> >> 2009/10/24 Bruno Baguette <bruno.baguette@gmail.com>: >>> >>> Hello ! >>> >>> I'm trying to write a little trigger function with variable arguments >>> quantity (at least one, but can be 2,3,4 arguments). >>> Theses arguments are fields name, so only varchar variable. >>> >>> Since it is a dynamic query, I use the EXECUTE statement as explained on >>> >>> <http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN> >>> >>> CREATE OR REPLACE FUNCTION delete_acl_trigger() RETURNS trigger AS >>> $delete_acl_trigger$ >>> DECLARE >>> BEGIN >>> FOR i IN 0 .. TG_NARGS LOOP >>> EXECUTE 'SELECT delete_acl(OLD.' || TG_ARGV[i] || ');'; >>> END LOOP; >>> RETURN OLD; >>> END; >>> $delete_acl_trigger$ LANGUAGE plpgsql; >>> >>> But, when the trigger is triggered, I receive this error message : >>> "Query failed: ERROR: OLD used in query that is not in a rule" >>> >>> How can I get the value of the OLD.' || TG_ARGV[i] field ? >> >> OLD is variable only in PLpgSQL procedure, - outside doesn't exists. >> If you have a 8.4, you can use USING clause >> >> EXPLAIN 'SELECT $1.' || TG_ARGV[i] INTO somevar USING OLD; >> >> >> http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN > > Hello Pavel, > > Thanks for your answer ! > > Unfortunately, I'm running PostgreSQL 8.3.7 (PostgreSQL 8.3.7 on > i586-mandriva-linux-gnu, compiled by GCC i586-mandriva-linux-gnu-gcc (GCC) > 4.2.3 (4.2.3-6mnb1)). > > Since 8.4.1 is not available for Mandriva 2009.1, I can only have this > PostgreSQL version. (I don't have root access on that server). > > Is there another way, usable in PostgreSQL 8.3.7, to solve my problem ? you can use plperl or plpython for this task. Pavel > > Many thanks in advance ! > > Kind Regards, > > -- > Bruno Baguette - bruno.baguette@gmail.com >
Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?
From
Bruno Baguette
Date:
Le 24/10/09 22:09, Pavel Stehule a écrit : > you can use plperl or plpython for this task. > > Pavel re-Hello Pavel, I always used plpgsql because I *thought* it was the most powerfull procedural language for stored procedures in PostgreSQL. The fact that PL/pgSQL is most documented than PL/Tcl and PL/Perl (and PL/Python *seems* to have very poor documentation) has probably made me wrong. Which one would you advise me to learn and use, instead of PL/pgSQL ? Can you explain me why does PL/Perl and PL/Python can does this task (using OLD in a dynamic query) where PL/pgSQL can't ? Once again, thanks a lot for your tips ! :-) Kind regards, -- Bruno Baguette - bruno.baguette@gmail.com
Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?
From
Pavel Stehule
Date:
2009/10/24 Bruno Baguette <bruno.baguette@gmail.com>: > Le 24/10/09 22:09, Pavel Stehule a écrit : >> >> you can use plperl or plpython for this task. >> >> Pavel > > re-Hello Pavel, > > I always used plpgsql because I *thought* it was the most powerfull > procedural language for stored procedures in PostgreSQL. plpgsql is best language for stored procedures, but simply it is not for all. There are some issue still, but some significant limits are removed in 8.4. > > The fact that PL/pgSQL is most documented than PL/Tcl and PL/Perl (and > PL/Python *seems* to have very poor documentation) has probably made me > wrong. > > Which one would you advise me to learn and use, instead of PL/pgSQL ? It depends on what you are know (if you known better perl or pthon). Usually I using mainly plpgsql and on some functions plperl and C. plpgsql is good language as glue of SQL statements, plperl is good for IO and CPAN libraries, and C should help with some processor intensive functions. > > Can you explain me why does PL/Perl and PL/Python can does this task (using > OLD in a dynamic query) where PL/pgSQL can't ? External procedures (like procedures in plperl or plpython) has not 100% integrated SQL. Next, SQL row is mapped to perl's array and it should be iterated. Plpgsql is similar to classic programming languages. In mostly older languages you cannot iterate over recordset.. plpgsql is static language like C, Pascal, ADA. Perl or Python are dynamic languages, so you can do some tasks very simple. And other not, because these languages are only on partially integrated with SQL. > > Once again, thanks a lot for your tips ! :-) with pleasure Pavel > > Kind regards, > > -- > Bruno Baguette - bruno.baguette@gmail.com > >
Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?
From
Bruno Baguette
Date:
Le 24/10/09 22:57, Pavel Stehule a écrit :
> 2009/10/24 Bruno Baguette <bruno.baguette@gmail.com>:
>> Which one would you advise me to learn and use, instead of PL/pgSQL ?
>
> It depends on what you are know (if you known better perl or pthon).
> Usually I using mainly plpgsql and on some functions plperl and C.
> plpgsql is good language as glue of SQL statements, plperl is good for
> IO and CPAN libraries, and C should help with some processor intensive
> functions.
Hello Pavel !
I've read the doc' about plperl and plpython, since I have notion of both.
Because plpython is not a trusted language, I finally took plperl.
In case somebody else does look for the same thing as I was looking for,
here's the new trigger function, rewritten using plperl :
####################################
CREATE OR REPLACE FUNCTION delete_acl_trigger() RETURNS trigger AS
$delete_acl_trigger$
if($_TD->{argc}>0)
{
for(my $i=0; $i < $_TD->{argc} ; $i++)
{
my $acl_id = $_TD->{old}{$_TD->{"args"}[i]};
my $rv = spi_exec_query("SELECT delete_acl($acl_id);");
# DEBUG TOOLS
# my $status = $rv->{status};
# elog(NOTICE, 'RESULTAT : '.$status);
}
return;
}
else
{
return;
}
$delete_acl_trigger$ LANGUAGE plperl;
####################################
Do I have to mention that it does perfectly what I expected ? :-)
If you have any comments or tips about that function, feel free to do so
in order to improve it ! :-)
Pavel, many thanks for your tips and explanations !
They were very helpfull ! :-)
Kind regards,
--
Bruno Baguette - bruno.baguette@gmail.com