I am trying to create a postgres function that just returns the friends that a user has in the database. It should just return the first and last names of all the user's friends. However, I keep getting this error:
[42601] ERROR: query has no destination for result data
It does not make sense to me because I am returning a table with all the first and last names.
Here is my function. Does anyone know why this would be the case? I could not find the answer anywhere else.
create or replace function getFriends(pEmail text)
returns TABLE(pkUser int, FirstName text, LastName text)
language plpgsql
as
$$
declare pk int;
begin
select "pkUser" into pk from users where "Email"=pEmail;
select
"pkUser", "FirstName", "LastName"
from users u
inner join friends f on u."pkUser" = f."User2ID"
where f."User1ID"=pk;
select "pkUser", "FirstName", u."LastName"
from users u
inner join friends f on u."pkUser" = (f."User1ID")
where f."User2ID"=pk;
end
$$;
Your final query doesn't "consume" its result. in PL/pgSQL, the result of a query either needs to be stored into a variable (like you did in your first query) or discarded using perform or returned from the function. To do that, use RETURN QUERY.
You can combine both query using a UNION ALL
create or replace function getFriends(pEmail text)
returns TABLE(pkUser int, FirstName text, LastName text)
language plpgsql
as
$$
declare pk int;
begin
select "pkUser" into pk from users where "Email"=pEmail;
return query
select "pkUser", "FirstName", "LastName"
from users u
inner join friends f on u."pkUser" = f."User2ID"
where f."User1ID"=pk
union all
select "pkUser", "FirstName", u."LastName"
from users u
inner join friends f on u."pkUser" = (f."User1ID")
where f."User2ID"=pk;
end
$$;
However, you can simplify the whole function to a single query and use language sql instead. The return query is not needed then.
create or replace function getFriends(pEmail text)
returns TABLE(pkUser int, FirstName text, LastName text)
language sql
as
$$
select "pkUser", "FirstName", "LastName"
from users u
inner join friends f on u."pkUser" = f."User2ID"
where f."User1ID" in (select "pkUser"
from users
where "Email" = pEmail)
union all
select "pkUser", "FirstName", u."LastName"
from users u
inner join friends f on u."pkUser" = f."User1ID"
where f."User2ID" in (select "pkUser"
from users
where "Email" = pEmail);
$$;
Your plpgsql function should return query to be valid.
But here I think that you can do what you want with a straight query, so just switch to sql and :
create or replace function getFriends(pEmail text)
returns table(pkUser int, FirstName text, LastName text)
language sql
as
$$
select u2."pkUser", u2."FirstName", u2."LastName"
from users u1
inner join friends f
on u1."pkUser" in (f."User1ID", f."User2ID")
inner join users u2
on u2."pkUser" in (f."User1ID", f."User2ID")
and u2."pkUser" <> u1."pkUser"
where u1."Email" = pEmail;
$$
This selects the record of the user whose email is given as an arugment to the function, then searches for all corresponding friends (whether in User1ID or User2ID) and finally return the friend user record.
Use SELECT ... INTO ... to store query results
OR
open a cursor
OR
use PERFORM ... in the unlikely case that you don't care about the result set.