How to create stored procedure with multiple select in Postgresql?

Question

I get a problem to create stored procedure in postgresql with multiple select, this is error that I get

ERROR: function result type must be specified SQL state: 42P13

and this is my query:

create or replace function user_info(
    functn integer
)
language sql 
as 
$$
begin
    
    if(functn = 1) then
        select email, user_name, address from user;
        
    else if(functn = 2) then
        select email, member_name, phone from member;
        
    else
        select email, admin_name, post_code from admin;
    
    end if;
    
end
$$;

please anyone help me to solve this problem, thanks.

You're doing CREATE FUNCTION without a RETURNS clause. (And also, your function doesn't do anything and returns nothing) — Bergi
– Bergi, Commented Dec 27, 2021 at 10:16

Zakaria · Accepted Answer · 2021-12-27 10:45:32Z

2

There are quite a few syntax errors in your function:

You didn't specify the return type.
Your function doesn't return anything.
Correct syntax is elsif instead of else if.

Try this:

create or replace function user_info(
    functn integer
)
returns table(email text, name text, contact text)
language plpgsql 
as 
$$
begin
    if functn = 1 then
        return query select email, user_name, address from user;
        
    elsif functn = 2 then
        return query select email, member_name, phone from member;
        
    else
        return query select email, admin_name, post_code from admin;
    
    end if;

end;
$$

edited Dec 27, 2021 at 10:45

answered Dec 27, 2021 at 10:32

Zakaria

4,7762 gold badges8 silver badges37 bronze badges

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

Collectives™ on Stack Overflow

How to create stored procedure with multiple select in Postgresql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related