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

Unrelated to your problem, but: you should really avoid those dreaded quoted identifiers. They are much more trouble than they are worth it. https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_upper_case_table_or_column_names — , Jun 25 '20 at 16:38
@a_horse_with_no_name Thank you for that link. I am trying to learn postgres through a side project and coming from mysql. I appreciate that. — Bailey Spell, Jun 25 '20 at 16:46

score 1 · Answer 1 · 2020-06-25T17:03:36.467

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

Okay, that makes sense why one of my previous functions worked and this one did not. Thank you so much. Is there any what to do both of the selects that join on the Friends table? — Bailey Spell, Jun 25 '20 at 16:41
Ah, I didn't see that those were indeed two different queries. You can combine them using UNION — , Jun 25 '20 at 17:06

GMB · Accepted Answer · 2020-06-25T17:10:16.153

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.

score 0 · Answer 3 · answered Jun 25 '20 at 16:36

0

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.

answered Jun 25 '20 at 16:36

pifor

7,419
2
8
16

Error when creating a function in Postgres

3 Answers3