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. wiki.postgresql.org/wiki/… — user330315
– user330315, Commented Jun 25, 2020 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
– Bailey Spell, Commented Jun 25, 2020 at 16:46

GMB · Accepted Answer · 2020-06-25 17:10:16Z

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.

edited Jun 25, 2020 at 17:10

answered Jun 25, 2020 at 16:41

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

score 1 · Accepted Answer · 2020-06-25 17:03:36Z

1

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

edited Jun 25, 2020 at 17:03

answered Jun 25, 2020 at 16:36

user330315

2 Comments

Bailey Spell Over a year ago

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?

user330315 Over a year ago

Ah, I didn't see that those were indeed two different queries. You can combine them using UNION

pifor · Accepted Answer · 2020-06-25 16:36:07Z

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, 2020 at 16:36

pifor

7,9722 gold badges12 silver badges18 bronze badges

Collectives™ on Stack Overflow

Error when creating a function in Postgres

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related