Returning table with specific parameters from a PostgreSQL function

Question

I have this function, with which I would like to return a table, which holds two columns: game_name and follow (this would be an integer 0 or 1):

CREATE OR REPLACE FUNCTION public.toggle2(uid numeric, gid NUMERIC)
    RETURNS TABLE (
        follow INT,
        game_name TEXT
    )
    LANGUAGE plpgsql
AS $$
    BEGIN
        IF NOT EXISTS(SELECT * 
                  FROM game_follows 
                 WHERE user_id = uid and game_id = gid)
                 THEN
    INSERT INTO game_follows(user_id, game_id) VALUES(uid, gid);
    follow := 1;
  ELSE
     DELETE FROM game_follows WHERE user_id = uid and game_id = gid;
     follow := 0;
  END IF;
 SELECT name INTO game_name FROM games WHERE id = gid;
    END;
$$
;

Sadly, the function returns empty values. I am using it as this:

SELECT * FROM toggle2(83, 12);

Please always disclose your version of Postgres. Also most helpful: a table definition (`CREATE TABLE` statement) showing data types and constraints. Most importantly, is there a `UNIQUE` constraint or index on `(user_id, game_id)`? And is concurrent write access possible? — Erwin Brandstetter, Apr 06 '20 at 21:52

score 0 · Accepted Answer · answered Apr 06 '20 at 22:21

0

A function declared to RETURN TABLE can return 0-n rows.
You must actively return rows, or nothing will be returned (no row). One way to do this:

RETURN NEXT; -- as last line before END;

There are other ways, see the manual.

However, it seems you want to return exactly one row every time. So rather use OUT parameters:

CREATE OR REPLACE FUNCTION public toggle2(uid numeric, gid numeric, OUT follow int, OUT game_name text) AS ...

Then it's enough to assign those OUT parameters, they are returned in the single result row automatically.

See:

answered Apr 06 '20 at 22:21

Erwin Brandstetter

605,456
145
1,078
1,228

More aspects of your function can be improved: not safe against concurrent writes; odd data types; inefficient; naming convention? But that's beyond the actual question - which is otherwise light on details. – Erwin Brandstetter Apr 06 '20 at 22:23
Also: the `IF...` is terrible. Could be rewritten into sane syntax. – wildplasser Apr 06 '20 at 22:29
@ErwinBrandstetter thanks for the response, why I would like to return table is because I would use the data as JSON in my node js backend. With out parameters it returns a string: "(1,'game_name')", which isn't very ideal result, and would rather have table return where it could be as a json format – r.Die Apr 07 '20 at 05:44
@r.Die: No, you still get a *row*. Call with `SELECT * FROM ...` like you did before. – Erwin Brandstetter Apr 07 '20 at 10:58
Thank you for clarification! – r.Die Apr 07 '20 at 12:49

Returning table with specific parameters from a PostgreSQL function

1 Answers1