Thread: Passing Arguments to a trigger function(Problem)

Hi,
I have a scenario in which I need to pass arguments to a trigger function.
The scenario is as follows:

I have a raw_email table.

A  trigger is written after every insert for each row.
Trigger function calls another procedure (SP_parse_email) internally.

Now, SP_parse_email needs 2 arguments (id, raw_email) that comes from the newly inserted row in the raw_email table.

This means, i need to propogate new.id and new.raw_email fields newly inserted in the raw_email table.

For this purpose, i write my trigger function as follows:

-------------------------------------------------------

CREATE OR REPLACE FUNCTION new_trigger_func()
  RETURNS "trigger" AS
$BODY$
BEGIN
   PERFORM sp_parse_email(TG_ARGV[0], TG_ARGV[1]);
   RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION new_trigger_func() OWNER TO postgres;

-------------------------------------------------------


I run it and works fine.

Now, I write my trigger as follows:

------------------------------------------------------------------------
CREATE TRIGGER new_test_trigger
  AFTER INSERT
  ON raw_email
  FOR EACH ROW
  EXECUTE PROCEDURE new_trigger_func(NEW.id);
------------------------------------------------------------------------


Running this script gives me the following error:

ERROR:  syntax error at or near "NEW" at character 115


Don't know what is the correct way to do this.

Thanks and regards,
~Jas

On Tue, 22 Aug 2006, Jasbinder Bali wrote:

> Hi,
> I have a scenario in which I need to pass arguments to a trigger function.
> The scenario is as follows:
>
> I have a raw_email table.
>
> A  trigger is written after every insert for each row.
> Trigger function calls another procedure (SP_parse_email) internally.
>
> Now, SP_parse_email needs 2 arguments (id, raw_email) that comes from the
> newly inserted row in the raw_email table.
>
> This means, i need to propogate new.id and new.raw_email fields newly
> inserted in the raw_email table.
>
> For this purpose, i write my trigger function as follows:
>
> -------------------------------------------------------
>
> CREATE OR REPLACE FUNCTION new_trigger_func()
>   RETURNS "trigger" AS
> $BODY$
> BEGIN
>    PERFORM sp_parse_email(TG_ARGV[0], TG_ARGV[1]);
>    RETURN NEW;
> END;
> $BODY$
>   LANGUAGE 'plpgsql' VOLATILE;

The function gets NEW (and some other values) automagically, so you should
just be able to use NEW.id and NEW.raw_email in the function body.  The
argument passing stuff is for passing other constant args into the trigger
(for example potentially to use the same trigger function for slightly
different cases where you can differentiate based on the argument given at
trigger creation).