triggers and parameters

From: Roger Mason <rmason(at)mun(dot)ca>
To: pgsql-novice <pgsql-novice(at)postgresql(dot)org>
Subject: triggers and parameters
Date: 2021-08-21 15:04:36
Message-ID: y65v93yq1ob.fsf@mun.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message David G. Johnston 2021-08-22 03:24:36 Re: triggers and parameters
Previous Message Roger Mason 2021-08-21 10:54:21 Re: plpgsql select into