Function's final statement must be SELECT or INSERT UPDATE DELETE RETURNING

Question

CREATE FUNCTION retrieve_add_friends(user_id text[]) 
RETURNS SETOF user_rows AS
$BODY$
BEGIN
    FOR user_rows IN EXECUTE SELECT * FROM user_details where user_id= $1
    LOOP

        FOR user_friends IN EXECUTE SELECT * FROM user_add_friends where user_id= $1
        LOOP
            IF user_rows.user_id!=user_friends.user_friend_id THEN
            RETURN NEXT user_rows;  
            END IF;
        END LOOP;

    RETURN;
    END LOOP;
    RETURN;
END
$BODY$
 language plpgsql VOLATILE;

When I execute this I get following error:

ERROR: return type mismatch in function declared to return user_details
DETAIL: Function's final statement must be SELECT or INSERT/UPDATE/DELETE RETURNING. CONTEXT: SQL function "retrieve_add_friends"

Can anyone help me out with this?

you are overcomplicating things. There is no need for a loop or even PL/pgSQL for this. — , Dec 20 '18 at 17:22
Providing the actual function call (and always your version of Postgres) would make it much easier to solve. — Erwin Brandstetter, Dec 20 '18 at 17:23
what data type is the column `user_details.user_id` if that is a single value, you can't compare it to an array. You probably want `where user_id = any($1)`. And if that column is an integer you shouldn't compare it to a `text` value — , Dec 20 '18 at 17:25

Erwin Brandstetter · Answer 1 · 2018-12-26T06:05:51.037

The function displayed is a PL/pgSQL function.

You are calling a different function, an SQL function, obviously (for which the error msg would make sense):

SQL function "retrieve_add_friends"

Same function name, but different arguments (and possibly in a different database schema). Are you aware of function overloading and its implications?

ERROR: function addgeometrycolumn is not unique

For a quick diagnosis:

SELECT oid::regprocedure AS function_signature, *
FROM   pg_proc
WHERE  proname = 'retrieve_add_friends';

All that aside, the function displayed has multiple errors and can be replaced with plain SELECT.

Function's final statement must be SELECT or INSERT UPDATE DELETE RETURNING

1 Answers1