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

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(dot)farber(at)gmail(dot)com> 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
>
>