SELECT ... INTO a variable within a postgres CTE

user_2036135 0
Viewed 1679

I want to do this inside a plpgsql function

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'

but Postgres throws the error

ERROR:  SELECT ... INTO is not allowed here

I'm guessing it's because the select ... into is in the CTE. But I can't find anything in the documentation or on the web about it. Maybe I just messed up the select ... into syntax?

select-into common-table-expression postgresql
2 Answers

SQL has no variables - they are part of a procedural language (e.g. PL/pgSQL), not the query language.

But I don't see the reason why you need one:

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'

If you are certain that query_only_for_select_into only returns a single row, you can use:

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

SELECT ... INTO variable is PL/pgSQL syntax, and you can only use it with the top level SELECT (the one that returns results. Think of it like that: PL/pgSQL runs an SQL statement and stores the result somewhere.

But you don't need that: simply include query_only_for_select_into in the FROM clause of the main query, then you can access its columns there.

user_64643080
Related