选择..INTO 一个 postgres CTE 中的变量

Question

我想在 plpgsql 函数中执行此操作

WITH set1 AS (
select * 
from table1
where ... -- reduce table1 to the small working set once for multiple reuse
), query_only_for_select_into AS (
select id 
into my_variable_declared_earlier
from set1 
where foo = 'bar'

)
select my_variable_declared_earlier as my_bar
, *
from set1
where foo <> 'bar'

但波斯特格雷斯抛出错误

ERROR:  SELECT ... INTO is not allowed here

我猜这是因为select ... into在 CTE 中。但是我在文档或网络上找不到任何有关它的内容。也许我只是搞砸了select ... into语法？

Answer 1

SQL没有变量 - 它们是过程语言(例如.PL/pgSQL)的一部分，而不是查询语言。

但我不明白你需要一个的原因：

WITH set1 AS (
select * 
from table1
where ... -- reduce table1 to the small working set once for multiple reuse
), query_only_for_select_into AS (
select id as my_variable_declared_earlier
from set1 
where foo = 'bar'
)
select qs.my_variable_declared_earlier as my_bar,
*
from set1
join query_only_for_select_into qs on ...
where foo <> 'bar'

如果确定query_only_for_select_into只返回一行，则可以使用：

select qs.my_variable_declared_earlier as my_bar,
*
from set1
cross join query_only_for_select_into qs
where foo <> 'bar'

Answer 2

SELECT ... INTO variable是PL/pgSQL语法，您只能将其与顶级SELECT(返回结果的语法)一起使用。可以这样想：PL/pgSQL 运行一个 SQL 语句并将结果存储在某个地方。

但是您不需要这样做：只需在主查询的FROM子句中包含query_only_for_select_into，然后您就可以在那里访问其列。