Error when creating a function in Postgres

Question

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
$$;

Answer 1

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.