public inbox for [email protected]  

help / color / mirror / Atom feed

triggers and parameters
3+ messages / 2 participants
[nested] [flat]

* triggers and parameters
@ 2021-08-21 15:04  Roger Mason <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: Roger Mason @ 2021-08-21 15:04 UTC (permalink / raw)
  To: pgsql-novice

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: triggers and parameters
@ 2021-08-22 03:24  David G. Johnston <[email protected]>
  parent: Roger Mason <[email protected]>
  0 siblings, 1 reply; 3+ messages in thread

From: David G. Johnston @ 2021-08-22 03:24 UTC (permalink / raw)
  To: Roger Mason <[email protected]>; +Cc: pgsql-novice

On Sat, Aug 21, 2021 at 7:13 PM Roger Mason <[email protected]> wrote:

> I want to pass a parameter (jid) that will be different for every
> invocation of 'foo_insert'.


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

David J.


^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: triggers and parameters
@ 2021-08-22 11:30  Roger Mason <[email protected]>
  parent: David G. Johnston <[email protected]>
  0 siblings, 0 replies; 3+ messages in thread

From: Roger Mason @ 2021-08-22 11:30 UTC (permalink / raw)
  To: pgsql-novice


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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

end of thread, other threads:[~2021-08-22 11:30 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-08-21 15:04 triggers and parameters Roger Mason <[email protected]>
2021-08-22 03:24 ` David G. Johnston <[email protected]>
2021-08-22 11:30   ` Roger Mason <[email protected]>

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