Declare type of setof bigint in plpgsql function and assign into from select union-CodePudding

The following code currently works on PostgreSQL 13.3 (via Supabase.io). Both functions get_owned_base_ids and get_bases_editable have return type of setof bigint:

CREATE FUNCTION get_owned_base_ids()
returns setof bigint
stable
language sql
as $$
  select id
  from bases
  where bases.owner_user_id = auth.uid();
$$;


-- CREATE FUNCTION get_bases_editable()
-- returns setof bigint
-- ... similar to get_owned_base_ids()
-- $$;


CREATE FUNCTION xyz (base_id bigint)
returns int
language plpgsql
as $$
BEGIN
  IF base_id not in (select get_owned_base_ids() UNION select get_bases_editable()) THEN
    -- note: actual function logic is simplified for this question
    return 1;
  END IF;
  
  return 0;
END;
$$;

Is it possible to define a setof bigint and assign that from the select union? Something like this:

CREATE FUNCTION xyz (base_id bigint)
returns int
language plpgsql
as $$
DECLARE
  allowed_base_ids bigint; -- needs to be a setof
BEGIN
  select into allowed_base_ids get_owned_base_ids() UNION select get_bases_editable();

  IF kv.base_id not in allowed_base_ids THEN
    -- note: actual function logic is simplified for this question
    return 1;
  END IF;
  
  return 0;
END;
$$;

CodePudding user response：

It usually does not make much sense and use much memory of the result set is large, but you can use an array:

DECLARE
   allowed_base_ids bigint[];
BEGIN
   allowed_base_ids := array(SELECT * FROM get_owned_base_ids()
                             UNION ALL
                             SELECT * FROM get_bases_editable());

   IF kv.base_id <> ALL (allowed_base_ids) THEN
      ...
   END IF;
END;