mardi 1 décembre 2020

Unable to return different queries based on if else condition in postgresql

I'm working on a postgresql function to return "3 different queries" as a result using if-else conditions, without combining all the tables. This is my primary goal. "I should not combine all the tables". Details are as below:

I've 5 tables in my DB they are

userTable

columns: id, fname, lname, data

adminTable

columns: id, fname, lname, data

superadminTable

columns: id, fname, lname, data

linkTable

columns: id, roleid, active

roleTable

columns: roleid, rolename

In order to achieve the desired result, i.e., a row containing all the details for id and active status, I've written a postgresql function, which is as shown below:

CREATE FUNCTION public.get_details_for_id(id integer)
RETURNS complex_details_type    --complex type with id, fname, lname, data, active as params
LANGUAGE 'plpgsql'

    
AS $BODY$
Declare role_and_status record;
Begin

--to find role and active status with id
select
    r.role_name, l.active
from
    linkTable l
    inner join roleTable r
        on r.roleid = l.roleid
into
    role_and_status
where
    l.id = get_details_for_id.id
limit 1;

if found then
    --My primary goal is to return three different queries, without joining all the tables (user, admin and superadmin)
    if (role_and_status.rolename = 'superadmin')
    then
        return query
        select
            t.*, role_and_status.active
        from 
            superadminTable t
        where
            t.id = get_details_for_id.id;
    elsif (role_and_status.rolename = 'admin')
    then
        return query
        select
            t.*, role_and_status.active
        from
            adminTable t
        where
            t.id = get_details_for_id.id;
    elsif role_and_status.rolename = 'user'
    then
        return query
        select
            t.*, role_and_status.active
        from
            userTable t
        where
            t.id = get_details_for_id.id;
    else
        return 'SQL Warning has occurred. Please check the input data';
    end if;
end if;
End;
$BODY$;

ALTER FUNCTION public.get_details_for_id(integer)
    OWNER TO postgres;

But when I try to save it I'm getting an error:

Error:cannot use RETURN QUERY in a non-SETOF function
LINE 28: return query
^

Please help me solve this. Thanks in advance.

Aucun commentaire:

Enregistrer un commentaire