Home > Back-end >  Function not returning table despite it being the return type
Function not returning table despite it being the return type

Time:05-30

Here is my function:

create or replace function like(likerId uuid, likeeId uuid)
returns table(is_matched bool, created_notification bool)
language plpgsql
as $$
    declare
        performed_like bool := (select swipeIfDifferent(likerId, likeeId, 'like'));
        liker_name text := (SELECT every_user.username from every_user where every_user_.id=likerId);
        is_matched bool := (
            SELECT EXISTS (
                SELECT 1 from user_like_or_dislike where
                user_like_or_dislike.owning_user_id=likeeId AND
                user_like_or_dislike.subject_user_id=likerId AND
                user_like_or_dislike.like_or_dislike='like'
            )
        );
    begin
        if performed_like then
            insert into notification (owning_user_id, subject_user_id, notification_kind_id, message, timestamp) values (likeeId, likerId, 2, liker_name   ' liked you!', current_timestamp);
        end if;
        return query select is_matched, performed_like;
    end;
$$;

Whenever I execute it, regardless of whether I use select * from like(...); or select like(...); it always outputs the following:

 like 
------
 f
(1 row)

Furthermore, it appears that the 'like' is never performed either (the function call swipeIfDifferent(...) when defining the performed_like variable). The return type of swipfIfDifferent(...) is bool.

CodePudding user response:

It's a bad idea to name your database objects the same names as system functions. When you think you're calling your function, you can't actually guarantee that the database will actually call it. Here is what is actually called in your case.

enter image description here

Therefore, rename your function, for example, to user_func_like, and the error will disappear. Or specify the schema name like user_schema.like(x,y). But better to use non-system names

  • Related