public inbox for [email protected]  

help / color / mirror / Atom feed

From: Roger Mason <[email protected]>
To: pgsql-novice <[email protected]>
Subject: triggers and parameters
Date: Sat, 21 Aug 2021 12:34:36 -0230
Message-ID: <[email protected]> (raw)

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

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: triggers and parameters
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox