From: | Alexander Farber <alexander(dot)farber(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | a column definition list is required for functions returning "record" |
Date: | 2016-08-26 15:20:37 |
Message-ID: | CAADeyWhYO4eoyTniakDMAtswHTHt5TwQPuPrfhNkjFpifSC+0w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2016-08-26 15:29:35 | Re: a column definition list is required for functions returning "record" |
Previous Message | Tom Lane | 2016-08-26 12:41:02 | Re: Understanding Postgres Memory Usage |