postgresql - 包含数据修改语句的 WITH 子句必须处于顶层 SQL 状态 : 0A000

Question

我写了一个函数，它使用 WITH 构造，像这样插入到表中:

CREATE OR REPLACE FUNCTION test_func()
RETURNS json AS
$BODY$
begin
 return (
   with t as (
     insert into t(id) 
     select 1
     returning *
    )
 select '{"a":"a"}'::json
 );
end;
$BODY$
 LANGUAGE plpgsql VOLATILE;
select test_func()

那是返回错误:

ERROR: WITH clause containing a data-modifying statement must be at the top level
SQL-состояние: 0A000

如果执行

   with t as (
     insert into t(id) 
     select 1
     returning *
    )
 select '{"a":"a"}'::json

结果没有错误。为什么会发生这种情况以及如何解决这个问题？

Answer 1

您正在对该查询进行子选择，这就是它不起作用的原因。这也行不通:

select * from (
   with t as (
     insert into t(id) 
     select 10
     returning *
    )
 select '{"a":"a"}'::json
) as sub

对此有一些解决方案。

a) 将其声明为返回 setof 并使用 return query

CREATE OR REPLACE FUNCTION test_func()
RETURNS setof json AS
$BODY$
begin
return query
   with t as (
     insert into t(id) 
     select 7
     returning *
    )
 select '{"a":"a"}'::json;
end;
$BODY$
 LANGUAGE plpgsql VOLATILE;

b) 声明为language sql

CREATE OR REPLACE FUNCTION test_func()
RETURNS json AS
$BODY$
   with t as (
     insert into t(id) 
     select 8
     returning *
    )
 select '{"a":"a"}'::json;
$BODY$
 LANGUAGE sql VOLATILE;

c) 在参数列表中声明输出变量并将结果分配给它们

CREATE OR REPLACE FUNCTION test_func(OUT my_out_var json)
AS
$BODY$
begin
   with t as (
     insert into t(id) 
     select 9
     returning *
    )
 select '{"a":"a"}'::json INTO my_out_var;
end;
$BODY$
 LANGUAGE plpgsql VOLATILE;