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