sql - PostgreSQL union a variable of type array to the result set of a query

Question

Is it possible to union a to union a variable to a select statement in PostgreSQL? I have a recursive function at the moment that in essence does this:

create or replace function call_recurrsive_function(ids bigint[])
.
.
select id from x where y
union call_recurrsive_function(select id from x where y)
.

I've recently made some changes that increase the complexity of the select by a lot, and to increase performance I'd like to run that query only once per function call and do something like

var = select id from x where y 
union call_recurrsive_function(var)

question from:https://stackoverflow.com/questions/65829019/postgresql-union-a-variable-of-type-array-to-the-result-set-of-a-query

Answer 1

You can try using a CTE (Common Table Expression). For example:

with
r as (
  select id from x where y
)
select *
from (
  select id from r
  union call_recurrsive_function(select id from r)
) x