Re: a column definition list is required for functions returning "record" - Mailing list pgsql-general

CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)
        RETURNS TABLE(
                out_gid integer,
                out_created integer,
                out_player1 integer,
                out_player2 integer,
                out_played1 integer,
                out_played2 integer,
                out_score1 integer,
                out_score2 integer,
                out_hand1 text,
                out_hand2 text,
                out_letters varchar[15][15],
                out_values integer[15][15],
                out_bid integer,
                out_last_tiles jsonb,
                out_last_score integer
        ) AS
$func$
        SELECT
                g.gid,
                EXTRACT(EPOCH FROM g.created)::int,
                g.player1,
                g.player2, -- can be NULL
                EXTRACT(EPOCH FROM g.played1)::int,
                EXTRACT(EPOCH FROM g.played2)::int,
                g.score1,
                g.score2,
                ARRAY_TO_STRING(g.hand1, ''),
                REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g'),
                g.letters,
                g.values,
                g.bid,
                m.tiles,
                m.score
        FROM words_games g LEFT JOIN words_moves m USING(mid)
        WHERE g.player1 = in_uid
        UNION SELECT
                g.gid,
                EXTRACT(EPOCH FROM g.created)::int,
                g.player2,
                g.player1, -- can not be NULL
                EXTRACT(EPOCH FROM g.played2)::int,
                EXTRACT(EPOCH FROM g.played1)::int,
                g.score2,
                g.score1,
                ARRAY_TO_STRING(g.hand2, ''),
                REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g'),
                g.letters,
                g.values,
                g.bid,
                m.tiles,
                m.score
        FROM words_games g LEFT JOIN words_moves m USING(mid)
        WHERE g.player2 = in_uid;

$func$ LANGUAGE sql;