plpgsql触发器-将一组动态id传递给插入查询(v9.6)

Question

从根本上来说，这就是我想要实现的。

CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
get404json text;
BEGIN
get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id IN(select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL)';
EXECUTE format(get404json);
RETURN NEW;
END
$$;

这是有效的，但它是有限的，因为我想对WHERE in select语句中的id执行额外的步骤，所以我想让集合/数组成为一个变量。

我从这个开始：

CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
get404ids text;
result404ids int ARRAY;
get404json text;
mark404processed text;
BEGIN
get404ids := 'select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL';
EXECUTE format(get404ids) INTO result404ids;
get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id = ANY (result404ids)';
EXECUTE format(get404json);
mark404processed := 'UPDATE public.event_404 SET processed = TRUE WHERE id IN(result404ids)';
RETURN NEW;
END
$$;

当触发器运行时，结果给出错误：

ERROR:  malformed array literal: "51"
DETAIL:  Array value must start with "{" or dimension information.
CONTEXT:  PL/pgSQL function transform404activities() line 10 at EXECUTE

这很有道理，因为它不是一个数组。

select id from public.event_404 WHERE created_at < (select now()) AND processed is NULL;
id 
----
51
52
53
50
(4 rows)

然而，当我介绍array_agg时，触发器希望从字面上使用变量，而不是数组的内容。

CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
get404ids text;
result404ids int ARRAY;
get404json text;
mark404processed text;
BEGIN
get404ids := 'select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL';
EXECUTE format(get404ids) INTO result404ids;
get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id = ANY (result404ids)';
EXECUTE format(get404json);
mark404processed := 'UPDATE public.event_404 SET processed = TRUE WHERE id IN(result404ids)';
RETURN NEW;
END
$$;

触发导致此错误：

ERROR:  column "result404ids" does not exist
LINE 1: ...event_name' from public.event_404 WHERE id = ANY (result404i...
^
QUERY:  insert into public.events_404_normalized(event_name) select json_data->>'event_name' from public.event_404 WHERE id = ANY (result404ids)
CONTEXT:  PL/pgSQL function transform404activities() line 12 at EXECUTE

我希望我的变量包含以下内容：

select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL;
array_agg   
---------------
{51,52,53,50}

我似乎错过了一些基本的东西，但我试图做的一切都会导致相同的"错误：列"result404ids"不存在"错误。

UPDATE我重写了触发器并删除了很多cruft。如预期：

CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
result404ids text;
BEGIN
EXECUTE 'select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL' INTO result404ids;
EXECUTE 'insert into public.events_404_normalized(event_name,language) select json_data->>''event_name'',json_data->>''language'' from event_404 WHERE id = ANY( '|| quote_literal(result404ids) ||')';
RETURN NEW;
END
$$;

更新2

根据Klin的建议，我的最终结果是：

CREATE OR REPLACE FUNCTION transform404activities() RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
get404ids text;
result404ids int ARRAY;
get404json text;
BEGIN
EXECUTE 'select array_agg(id) from public.event_404 WHERE created_at < (select now()) AND processed is NULL' INTO result404ids;
get404json := 'insert into public.events_404_normalized(event_name,language) select json_data->>''event_name'', json_data->>''language'' from public.event_404 WHERE id = ANY ($1)';
execute get404json using result404ids;
RETURN NEW;
END
$$;

Answer 1

使用execute ... using ...:

...
get404json := 'insert into public.events_404_normalized(event_name) select json_data->>''event_name'' from public.event_404 WHERE id = ANY ($1)';
execute get404json using result404ids;
...

请参阅文档中的执行动态命令。