postgresqlcommon-table-expressionselect-into

SELECT ... INTO a variable within a postgres CTE


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?


Solution

  • 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'