Postgres Pro
Downloads
Home   >   mailing lists

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

From Cachique
Subject Re: a column definition list is required for functions returning "record"
Date
Msg-id CAEfeRhVO=YaY2ZESMuONNayOSE+_Vk9SkF=esej4DR0AyfCPfA@mail.gmail.com
Whole thread Raw
In response to a column definition list is required for functions returning "record"  (Alexander Farber <alexander.farber@gmail.com>)
Responses Re: a column definition list is required for functions returning "record"  (Merlin Moncure <mmoncure@gmail.com>)
List pgsql-general
Tree view
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@gmail.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


pgsql-general by date:

Previous
From: Alexander Farber
Date:
Subject: Re: a column definition list is required for functions returning "record"
Next
From: Merlin Moncure
Date:
Subject: Re: a column definition list is required for functions returning "record"