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
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 |