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