postgresql - 将 pl/pgSQL select 语句转换为插入语句

Question

我试图将此选择查询转换为插入查询以将结果插入到另一个表中，但 PostgreSQL 告诉我不行。此查询有效并返回“报告”数据类型:

drop function counties();

create or replace function counties()
returns table(code varchar(16), county varchar(50), count bigint) as $$
DECLARE
    new_version_number varchar(50) := concat('gcversa00', MAX("versionnumber")) from "gcdefault"."versionhistory";
    old_version_number varchar(50) := concat('gcversa00', MAX("versionnumber"-2)) from "gcdefault"."versionhistory";
BEGIN RETURN QUERY EXECUTE
format(
    'with cte_current as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),

         cte_new     as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),


        cte_union as (select code, county from cte_current
        union all
        select code, county from cte_new)

    select code,county, count(*) as count
    from cte_union
    group by code, county
    Having count (*) <> 2', new_version_number, old_version_number
    );
END;
$$
LANGUAGE plpgsql;

当我将其转换为插入查询并调用 select counties() 时:

drop function counties();

create or replace function counties()
returns table(code varchar(16), county varchar(50), count bigint) as $$
DECLARE
    new_version_number varchar(50) := concat('gcversa00', MAX("versionnumber")) from "gcdefault"."versionhistory";
        old_version_number varchar(50) := concat('gcversa00', MAX("versionnumber"-2)) from "gcdefault"."versionhistory";
BEGIN RETURN QUERY EXECUTE
format(
    '
    with cte_current as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),

         cte_new     as (select distinct a.code as code, b.countyname as county from %I.servicearea a, public.counties b 
                 where st_intersects(a.geom,b.geom) = True group by a.code, b.countyname),


        cte_union as (select code, county from cte_current
        union all
        select code, county from cte_new)

    insert into county_check (code, county, count)
    select code, county, count(*) as count from cte_union
    group by code, county
    Having count (*) <> 2', new_version_number, old_version_number
    );
END;
$$
LANGUAGE plpgsql;

这是我得到的错误:

ERROR:  cannot open INSERT query as cursor
CONTEXT:  PL/pgSQL function counties() line 5 at RETURN QUERY

我怎样才能使它像我现在布局的方式一样作为插入语句工作(如果可能的话)？我查看了创建一些临时表而不是在查询中使用 cte，然后使用 select into 循环，但我找不到任何我可以使用的如此复杂的示例。

Answer 1

您可以添加 RETURNING 来修复它(实际返回声明的返回类型)，例如:

t=# create table so(i int);
CREATE TABLE
t=# create or replace function f() returns table (i int) as
$$
begin
return query execute format ('
with c(c) as (select 2)
, i as (insert into so select c from c returning *)
select * from i');
end;
$$
language plpgsql;
CREATE FUNCTION
t=# select f();
 f
---
 2
t=# select * from so;
 i
---
 2
(1 row)

但在您的情况下，为什么不只是:

insert into county_check (code, county, count) select * from counties()