triggers and parameters

Поиск
Список
Период
Сортировка
От Roger Mason
Тема triggers and parameters
Дата
Msg-id y65v93yq1ob.fsf@mun.ca
обсуждение исходный текст
Ответы Re: triggers and parameters  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список 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



В списке pgsql-novice по дате отправления:

Предыдущее
От: Roger Mason
Дата:
Сообщение: Re: plpgsql select into
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: triggers and parameters