Thread: triggers and parameters

Hello,

I have a trigger function:

CREATE OR REPLACE FUNCTION foo_insert ()
  RETURNS TRIGGER
  AS $$
DECLARE
BEGIN
  INSERT INTO foo
  SELECT
    *
  FROM
    -- how can I pass in the JID given that a trigger function can't take arguments?
    get_info ( jid );
  RETURN new;
END;
$$
LANGUAGE 'plpgsql';

that calls a function that returns a table:

CREATE OR REPLACE FUNCTION get_info (id text)
  RETURNS TABLE (
    jid text,
    "timestamp" text,
    tabular_info text
  )
  AS $function$
BEGIN
  RETURN query WITH a AS (
    SELECT
      public.results.jid AS jid,
      public.results. "timestamp" AS "timestamp",
      regexp_split_to_table(info_out, '\n') AS tabular_info
    FROM
      public.results
    WHERE
      public.results.jid = id
)
  SELECT
    *
  FROM
    a RETURN;
END;
$function$
LANGUAGE plpgsql;

and a trigger:

CREATE TRIGGER btrigger_foo_populate
  AFTER INSERT ON results
  FOR EACH statement
  EXECUTE PROCEDURE foo_insert ();

I want to pass a parameter (jid) that will be different for every
invocation of 'foo_insert'.  I can't see any way to do this in plpgsql.
If it can't be done in plpgsql, is there some mechanism to accomplish
the task?

Thanks,
Roger

David G. Johnston writes:

> If jid is on the table "results" then you are good [1].  If not, how
> exactly would expect that to work?
>
> https://www.postgresql.org/docs/current/plpgsql-trigger.html#PLPGSQL-DML-TRIGGER

OK, I think I understand better now.  The NEW record holds the new data
and the trigger function can extract the items it needs from that
record.

Thanks - inviting me to read the docs more carefully was just what I
needed.

Roger