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?

user330315 · Accepted Answer · 2021-03-11 18:45:48Z

2

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'

answered Mar 11, 2021 at 18:45

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

poshest Over a year ago

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.

Laurenz Albe · Accepted Answer · 2021-03-11 18:48:48Z

1

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.

answered Mar 11, 2021 at 18:48

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Collectives™ on Stack Overflow

SELECT ... INTO a variable within a postgres CTE

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related