SELECT ... INTO a variable within a postgres CTE

Question

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?

Answer 1

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'