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?

score 2 · Accepted Answer · answered Mar 11 '21 at 18:45

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'

You're quite right... I forgot to mention that this was inside a plpgsql function. I amended the question. I ended up also doing `query_only_for_select_into` like you have it, but I had some intuition that that a cross join (even with a single row, which I do have) would be slower than a variable reference in the select, hence the question. — poshest, Mar 11 '21 at 19:27

score 1 · Answer 2 · answered Mar 11 '21 at 18:48

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.

SELECT ... INTO a variable within a postgres CTE

2 Answers2