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

case, plpgsql, postgresql

Solution

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.

Problem

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*/ ```

Original source