CODEWITHSUNDEEP
sqlpostgresqlfunctioncase
boxx
boxx

Reputation: 1121

How to implement CASE returning multiple rows in PostgreSQL

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?

Upvotes: 0

Views: 1151

Answers (1)

user330315
user330315

Reputation:

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.

Upvotes: 2

Related Questions