Re: a column definition list is required for functions returning "record"

[Cachique <cachique@xxxxxxxxx>]

Hi
From the documentation... ( https://www.postgresql.org/docs/current/static/sql-select.html )

'Function calls can appear in the FROM clause. (This is especially useful for functions that return result sets, but any function can be used.) This acts as though the function's output were created as a temporary table for the duration of this single SELECT command...
If the function has been defined as returning the record data type, then an alias or the key word AS must be present, followed by a column definition list in the form ( column_name data_type [, ... ]). The column definition list must match the actual number and types of columns returned by the function.'

You need to use 'returns table' syntax or to add an alias in your query.

Something like

select * from words_select_games(1) as (gid type, created type, player1 type, ...);

Check for the correct column types

Regards,

Walter

On Fri, Aug 26, 2016 at 11:20 AM, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:

Good afternon,

in 9.5.3 I have defined the following custom function:

CREATE OR REPLACE FUNCTION words_select_games(IN in_uid integer)

        RETURNS SETOF RECORD AS

$func$

BEGIN

        RETURN QUERY SELECT 

                g.gid AS gid, 

                EXTRACT(EPOCH FROM g.created)::int AS created,

                g.player1 AS player1,

                COALESCE(g.player2, 0) AS player2,

                COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played1,

                COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS played2,

                ARRAY_TO_STRING(g.hand1, '') AS hand1,

                REGEXP_REPLACE(ARRAY_TO_STRING(g.hand2, ''), '.', '?', 'g') AS hand2,

                g.letters AS letters,         /* is a varchar[15][15] */

                g.values AS values,        /* is an integer[15][15] */

                g.bid AS bid,

                m.tiles AS last_tiles,

                m.score AS last_score

        FROM words_games g LEFT JOIN words_moves m USING(mid)

        WHERE g.player1 = in_uid

        UNION SELECT

                g.gid AS gid, 

                EXTRACT(EPOCH FROM g.created)::int AS created,

                g.player2 AS player1,

                COALESCE(g.player2, 0) AS player1,

                COALESCE(EXTRACT(EPOCH FROM g.played2)::int, 0) AS played1,

                COALESCE(EXTRACT(EPOCH FROM g.played1)::int, 0) AS played2,

                ARRAY_TO_STRING(g.hand2, '') AS hand1,

                REGEXP_REPLACE(ARRAY_TO_STRING(g.hand1, ''), '.', '?', 'g') AS hand2,

                g.letters AS letters,

                g.values AS values,

                g.bid AS bid,

                m.tiles AS last_tiles,

                m.score AS last_score

        FROM words_games g LEFT JOIN words_moves m USING(mid)

        WHERE g.player2 = in_uid;

END

$func$ LANGUAGE plpgsql;

but calling it gives me errors:

words=> select * from words_select_games(1);

ERROR:  a column definition list is required for functions returning "record"

LINE 1: select * from words_select_games(1);

                      ^

words=> select gid, bid from words_select_games(1);                                                                                                                               ERROR:  a column definition list is required for functions returning "record"

LINE 1: select gid, bid from words_select_games(1);

                             ^

I have also unsuccessfully tried

        RETURNS SETOF words_games, words_moves AS

and without the comma:

        RETURNS SETOF words_games words_moves AS

How would you recommend to fix my declaration problem please?

Regards

Alex