CODEWITHSUNDEEP
postgresqlstored-proceduresplpgsql
Anita Wulandari
Anita Wulandari

Reputation: 57

Function returning set of records from one of two queries depending on argument

I've got a problem in this function:

create function fruit(proses varchar)
returns table("FRUIT" varchar, "ID" integer, "FRUITCOLOR" varchar) as
            -- i think i miss something in this part
$$
begin
  if proses = 'view1' then
    select fruit, id from table;
  elseif proses = 'view2' then
    select fruit, id, fruitcolor from table
  end if;
end; $$ language plpgsql;

I want to use one of two SELECT queries but it doesn't work, I think the mistake is in returns table but I don't know where exactly.

Upvotes: 1

Views: 81

Answers (1)

Patrick
Patrick

Reputation: 32159

It appears that you want to return a set of rows from a table through a function. Your code is mostly correct, but you forgot to RETURN QUERY from the function:

create function fruit(proses varchar)
returns table("FRUIT" varchar, "ID" integer, "FRUITCOLOR" varchar) as $$
begin
  if proses = 'view1' then
    RETURN QUERY select fruit, id, NULL from table;
  elseif proses = 'view2' then
    RETURN QUERY select fruit, id, fruitcolor from table;
  end if;
  RETURN;
end; $$ language plpgsql;

Note that you can only return a single "type" from any given function, so in case proses = 'view1', you should return NULL as the third column.

You can now use your function as if it were any other table or view:

SELECT id, fruit FROM fruit('view1');

Upvotes: 1

Related Questions