IronPort-SDR: 
 vLIH1h/b73JC+ATzkQfOqXNZ67g8JiIXcjMku/K0QTu24f/WiSokUEMzDL3NFl5U8YN/yBK5QQ
 9mte0jFeBnIofUPprRPSYJYQ6aJGWue3TyoPwzQ7OjbRd8yD3kwfOxFHtoBqiaO5m8gN2NWRfD
 CcIq4oo0U+1GvJ9SpeRnOtDVggDbJN4PZe9hps6a543oqUobotiqYdf/oxzxCIT6PrE8B00yi9
 AL4gI0rGLhvDZlL6fYWXQWd7pq5/bHheycrvK56NewtjuPasxDiSC6+OMfotRSVkeWRFBAgGtd
 CpQ=
IronPort-HdrOrdr: =?us-ascii?q?A9a23=3AZJT2XqF3y8yzocS9pLqE9ceALOsnbusQ8z?=
 =?us-ascii?q?AXPiFKKSC9Hfb2qynDpp4mPHzP6Qr5OktPpTnoAsDpfZq2z/JICOcqUIuKYB?=
 =?us-ascii?q?Proy+hIo1k8OLZqAHdJw=3D=3D?=
User-agent: mu4e 1.5.6; emacs 27.2
From: Roger Mason <rmason@mun.ca>
To: pgsql-novice <pgsql-novice@postgresql.org>
Subject: triggers and parameters
Date: Sat, 21 Aug 2021 12:34:36 -0230
Message-ID: <y65v93yq1ob.fsf@mun.ca>
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: <https://www.postgresql.org/message-id/y65v93yq1ob.fsf%40mun.ca>
Precedence: bulk

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