Re: Passing parameters to triggers

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