CODEWITHSUNDEEP
postgresqlcaseplpgsql
Sarfaraz Makandar
Sarfaraz Makandar

Reputation: 6753

PostgreSQL: How to use SELECT statement within CASE expression in function?

Can we able to use SELECT statement within CASE conditional expression in function?
I have tried the following function to accomplish the above task.

Example: I have a function which is used to display table containing some rows. 

create or replace function test(n integer) 
returns table (name text,city text) as
$body$
begin
     case n when 1 then
     select * from table1

     when 2 then
     select * from table2

     when 3 then
     select * from view1

     end;
end;
$body$
language plpgsql;

--Calling function 

select * from test(1);  
/*have to show details of table1*/

select * from test(2);
/*have to show details of table2*/

select * from test(3);
/*have to display details of view1*/

Upvotes: 5

Views: 6689

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659277

Actually, there is a plpgsql CASE statement, not to be confused with the SQL CASE expression:

CREATE OR REPLACE function test(n integer) 
  RETURNS TABLE (name text, city text) AS
$func$
BEGIN

CASE n
WHEN 1 THEN
    RETURN QUERY SELECT t.name, t.city FROM table1 t;
WHEN 2 THEN
    RETURN QUERY SELECT t.foo, t.bar   FROM table2 t;
WHEN 3 THEN
    RETURN QUERY SELECT t.bar, t.bamm  FROM view1 t;
END CASE;

END
$func$ LANGUAGE plpgsql;

  • If you declare your function as RETURNS TABLE (name text, city text), then your SELECT statements should have a column list with matching types.
    If on the other hand you want to SELECT *, declare the function as RETURNS SETOF table1 accordingly.

  • When naming columns in the return type, those variable are visible in the function body. Be sure to table-qualify column names that would conflict with same names. So t.name instead of just name.

  • Column names from queries inside the function are not visible outside. Only the declared return type. So names don't have to match, just data types.

Either way, I suggest to simplify things:

CREATE OR REPLACE function test(n integer) 
  RETURNS SETOF table1 AS
$func$

SELECT * FROM table1 t WHERE n = 1
UNION ALL
SELECT * FROM table2 t WHERE n = 2
UNION ALL
SELECT * FROM view1  t WHERE n = 3;

$func$ LANGUAGE sql;

Same result. Just as fast. SQL or PL/pgSQL function is a matter of taste and some other details. PL/pgSQL is probably faster for stand-alone calls. SQL can more easily be nested.

Upvotes: 3

Craig Ringer
Craig Ringer

Reputation: 325161

For this you need an IF statement and RETURN QUERY, e.g.

create or replace function test(n integer) 
returns table (name text,city text) as
$body$
begin
     IF n = 1 THEN
         RETURN QUERY select * from table1;
     ELIF n = 2 THEN
         RETURN QUERY select * from table2;
     ELIF n = 3 THEN
         RETURN QUERY select * from view1;
     END IF;
end;
$body$
language plpgsql;

It's a very weird thing to want to do, though.

Upvotes: 2

Related Questions