Re: Passing parameters to triggers

I have a severe problem when working with PL/pgSQL triggers. Somehow the
problem seems to be strange. Here is the code:

CREATE OR REPLACE FUNCTION checkint () RETURNS opaque AS
'
BEGIN
IF TG_OP = ''DELETE'' THEN
RAISE NOTICE ''% wert'', TG_ARGV[0];
DELETE FROM TG_ARGV[0];
END IF;
RETURN OLD;
END;
' LANGUAGE 'plpgsql';

CREATE TRIGGER trig_a_b AFTER DELETE OR UPDATE ON a
FOR EACH ROW EXECUTE PROCEDURE checkint('b');

DELETE FROM a;

When running the code I get a strange problem. The RAISE NOTICE command
works but somehow the DELETE statements displays an error.

NOTICE: b wert
NOTICE: Error occurred while executing PL/pgSQL function checkint
NOTICE: line 4 at SQL statement
ERROR: parser: parse error at or near "$1"

Does anybody know what I can do?

Hans

Hans-Jürgen Schönig wrote:
> I have a severe problem when working with PL/pgSQL triggers. Somehow the
> problem seems to be strange. Here is the code:
>
> CREATE OR REPLACE FUNCTION checkint () RETURNS opaque AS
> '
> BEGIN
> IF TG_OP = ''DELETE'' THEN
> RAISE NOTICE ''% wert'', TG_ARGV[0];
> DELETE FROM TG_ARGV[0];
> END IF;
> RETURN OLD;
> END;
> ' LANGUAGE 'plpgsql';
>
> CREATE TRIGGER trig_a_b AFTER DELETE OR UPDATE ON a
> FOR EACH ROW EXECUTE PROCEDURE checkint('b');
>
> DELETE FROM a;
>
> When running the code I get a strange problem. The RAISE NOTICE command
> works but somehow the DELETE statements displays an error.

Object names (tables, attributes, sequences, etc.) cannot be
parameterized directly in procedural languages. You can get
the wanted result with

EXECUTE ''DELETE FROM '' || quote_ident(TG_ARGV[0]);

Jan

>
> NOTICE: b wert
> NOTICE: Error occurred while executing PL/pgSQL function checkint
> NOTICE: line 4 at SQL statement
> ERROR: parser: parse error at or near "$1"
>
> Does anybody know what I can do?
>
> Hans
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com

On Thu, 14 Mar 2002, [iso-8859-1] Hans-Jrgen Schnig wrote:

> I have a severe problem when working with PL/pgSQL triggers. Somehow the
> problem seems to be strange. Here is the code:
>
> CREATE OR REPLACE FUNCTION checkint () RETURNS opaque AS
> '
> BEGIN
> IF TG_OP = ''DELETE'' THEN
> RAISE NOTICE ''% wert'', TG_ARGV[0];
> DELETE FROM TG_ARGV[0];

EXECUTE ''DELETE FROM '' || TG_ARGV[0];
will probably do what you want. You can't directly
substitute variables for tables, columns etc but you
can use execute to run a command string.

Hans-Jürgen Schönig <hs(at)cybertec(dot)at> writes:

> I have a severe problem when working with PL/pgSQL triggers. Somehow the
> problem seems to be strange. Here is the code:
>
> CREATE OR REPLACE FUNCTION checkint () RETURNS opaque AS
> '
> BEGIN
> IF TG_OP = ''DELETE'' THEN
> RAISE NOTICE ''% wert'', TG_ARGV[0];
> DELETE FROM TG_ARGV[0];

You need to construct a query on the fly using EXECUTE, since queries
are preparsed in PL/pgSQL. So it would be:

EXECUTE ''DELETE FROM '' || TG_ARGV[0] ;

-Doug
--
Doug McNaught Wireboard Industries http://www.wireboard.com/

Custom software development, systems and network consulting.
Java PostgreSQL Enhydra Python Zope Perl Apache Linux BSD...