5

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*/
Improve this question

2 Answers 2

Reset to default
3

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.

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

2 Comments

Thank You so much. A little confusion here. UNION ALL need all columns to be same, but when i want to display a table which has different column then is it possible here?
@Meem: The number of columns and their date types have to match (or an automatic cast is possible between closely related types). Or you have to fill in constants and cast to make them match. The same is true for the function. For a UNION query only the names of the first leg are used, names in later legs of a UNION query are not irrelevant. For the function, only names of the declared RETURN type are used.
2

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.

Improve this answer

3 Comments

One more question, if i want to show table2 rows which have different column name from above return table then how to display that.
Please post a new question if you want to ask something else; Stack Overflow tries to stay to one major topic per question.
Ok will take care about it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.