How to implement CASE returning multiple rows in PostgreSQL

Question

I have a set of functions with the following pattern:

create or replace function example_1(x1 integer)
returns table (y integer) as $$
    select ...
$$ language sql immutable;

create or replace function example_2(x1 integer, x2 integer)
returns table (y integer) as $$
    select ...
$$ language sql immutable;

create or replace function example_N(x1 integer, x2 integer, ..., xN integer)
returns table (y integer) as $$
    select ...
$$ language sql immutable;

I want to create a single function that encapsulate the functions above in the following way:

create or replace function example(x integer[])
returns table (y integer) as $$
    select case length(x, 1)
        when 1 then example_1(x[1])
        when 2 then example_2(x[1], x[2])
        ...
        when N then select example_2(x[1], x[2], ..., x[N])
    end
$$ language sql immutable;

The problem is that set-returning functions are not allowed in CASE.

ERROR: set-returning functions are not allowed in CASE LINE 9: else (example_2(x[1], x[2]^ HINT: You might be able to move the set-returning function into a LATERAL FROM item. SQL state: 0A000 Character: 575

Is there a way how else I can implement example function?

That "pattern" looks wrong to begin with. Why don't you create a single function with an array as the input parameter (or a variable number of arguments)? — user330315
– user330315, Commented Jun 20, 2018 at 9:34

user330315 · Accepted Answer · 2018-06-20 09:32:53Z

2

You need PL/pgSQL for that:

create or replace function example(x integer[])
  returns table (y integer) 
as 
$$
begin
  if length(x, 1) = 1 then  
     return query 
       select * from  example_1(x[1]);
  elsif length(x, 1) = 2 then 
     return query 
       select * from  example_2(x[1], x[2]);
  elsif length(x, 1) = 3 then 
     return query 
       select * from  example_2(x[1], x[2], x[3]);
  end if;
$$ 
language plpgsql;

Note that immutable for a function that accesses the database is wrong. It should be stable

An single function with a variable number of arguments (or an array as the input) instead of example_1, example_2, ... might be better. But there is no enough information in your example to know that for certain.

answered Jun 20, 2018 at 9:32

user330315

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

Collectives™ on Stack Overflow

How to implement CASE returning multiple rows in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related