Home > mailing lists

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

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

From

Alexander Farber

Date:

26 August 2016, 18:20:44

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

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

From

Tom Lane

Date:

26 August 2016, 18:29:39

Alexander Farber <alexander.farber@gmail.com> writes:
> 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

> I have also unsuccessfully tried
>         RETURNS SETOF words_games, words_moves AS

> How would you recommend to fix my declaration problem please?

I think you are looking for the RETURNS TABLE syntax.

            regards, tom lane

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

From

Alexander Farber

Date:

26 August 2016, 18:32:16

Thank you, I was just wondering if there is a simpler way... but ok

On Fri, Aug 26, 2016 at 5:29 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

I think you are looking for the RETURNS TABLE syntax.

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

From

Cachique

Date:

26 August 2016, 18:50:57

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

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

From

Merlin Moncure

Date:

27 August 2016, 00:55:11

On Fri, Aug 26, 2016 at 10:50 AM, Cachique <cachique@gmail.com> wrote:
> 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

In this case it's probably better to have the function return a table
type, RETURNS table, or define the output with OUT variables.  The
record defining syntax at time of query:

SELECT foo() AS (a int, b text);

Typically should only be used when the function does not have a well
defined return structure.  dlbink is a good example, since the
structure of the input query directly controls the stucture of the
return type.  The sever has no way to deal with that when the query is
parsed and planned, so you have to help it out.

merlin

merlin

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

From

Jim Nasby

Date:

29 August 2016, 02:59:51

On 8/26/16 10:32 AM, Alexander Farber wrote:
> Thank you, I was just wondering if there is a simpler way... but ok

It would be nice if there was a way to pass dynamically formed records
around, similar to how you can pass the results of row() around. Someone
else has actually be asking about this at
https://github.com/decibel/pg_lambda/issues/1.

BTW, there's no advantage I can think of to using plpgsql just to return
the output of a query. You'd be better off using a SQL function instead.

Actually, I guess the plan for the plpgsql version would be cached; I'm
not sure if the same is true for SQL functions. But you'd probably need
a more complex query for that to be a win over the lighter weight nature
of SQL functions.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

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

From

Pavel Stehule

Date:

29 August 2016, 08:03:38

2016-08-29 1:59 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:

On 8/26/16 10:32 AM, Alexander Farber wrote:
Thank you, I was just wondering if there is a simpler way... but ok

It would be nice if there was a way to pass dynamically formed records around, similar to how you can pass the results of row() around. Someone else has actually be asking about this at https://github.com/decibel/pg_lambda/issues/1.

Probably there is a space to be PLpgSQL more flexible - but there are limits - PLpgSQL is black box for SQL engine, and when output is any record type, then SQL engine knows zero about returning data structure in preprocessing time. It is reason, why exists statement CALL (not implemented in Postgres) - where these information should not be necessary.

BTW, there's no advantage I can think of to using plpgsql just to return the output of a query. You'd be better off using a SQL function instead.

Actually, I guess the plan for the plpgsql version would be cached; I'm not sure if the same is true for SQL functions. But you'd probably need a more complex query for that to be a win over the lighter weight nature of SQL functions.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

From

Alexander Farber

Date:

29 August 2016, 11:15:30

Thank you for your comments!
I have switched to SQL function now
(I didn't realize it is better performancewise) -

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;

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

From

Tom Lane

Date:

29 August 2016, 14:28:17

Pavel Stehule <pavel.stehule@gmail.com> writes:
> 2016-08-29 1:59 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
>> It would be nice if there was a way to pass dynamically formed records
>> around, similar to how you can pass the results of row() around. Someone
>> else has actually be asking about this at https://github.com/decibel/pg_
>> lambda/issues/1.

> Probably there is a space to be PLpgSQL more flexible - but there are
> limits - PLpgSQL is black box for SQL engine, and when output is any record
> type, then SQL engine knows zero about returning data structure in
> preprocessing time.

Exactly.  You can pass anonymous record types around today, as long as you
don't do anything that requires knowing what their contents are, either in
the function or in the calling query:

regression=# create function foor(int,int) returns record language sql as $$ select row($1,$2); $$;
CREATE FUNCTION
regression=# select foor(23,45);
  foor
---------
 (23,45)
(1 row)

regression=# create function plr(int,int) returns record language plpgsql as $$begin return row($1,$2); end; $$;
CREATE FUNCTION
regression=# select plr(23,45);
   plr
---------
 (23,45)
(1 row)

What you can't do is, eg,

regression=# select * from plr(23,45);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from plr(23,45);
                      ^

because the parser has no basis on which to expand the "*".  The column
definition list is exactly a hack for telling it that.

            regards, tom lane

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

From

Merlin Moncure

Date:

29 August 2016, 20:46:24

On Mon, Aug 29, 2016 at 6:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> 2016-08-29 1:59 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
>>> It would be nice if there was a way to pass dynamically formed records
>>> around, similar to how you can pass the results of row() around. Someone
>>> else has actually be asking about this at https://github.com/decibel/pg_
>>> lambda/issues/1.
>
>> Probably there is a space to be PLpgSQL more flexible - but there are
>> limits - PLpgSQL is black box for SQL engine, and when output is any record
>> type, then SQL engine knows zero about returning data structure in
>> preprocessing time.
>
> Exactly.  You can pass anonymous record types around today, as long as you
> don't do anything that requires knowing what their contents are, either in
> the function or in the calling query:
>
> regression=# create function foor(int,int) returns record language sql as $$ select row($1,$2); $$;
> CREATE FUNCTION
> regression=# select foor(23,45);
>   foor
> ---------
>  (23,45)
> (1 row)
>
> regression=# create function plr(int,int) returns record language plpgsql as $$begin return row($1,$2); end; $$;
> CREATE FUNCTION
> regression=# select plr(23,45);
>    plr
> ---------
>  (23,45)
> (1 row)
>
> What you can't do is, eg,
>
> regression=# select * from plr(23,45);
> ERROR:  a column definition list is required for functions returning "record"
> LINE 1: select * from plr(23,45);

Another tactic is to use json in such cases:
create function fooj(int,int) returns json language sql as $$ select
to_json((select q from (select $1 as a, $2 as b) q)); $$;
CREATE FUNCTION
postgres=# select fooj(3,4);
     fooj
───────────────
 {"a":3,"b":4}
(1 row)

the advantage here is that you're not quite as boxed in: stuff like
postgres=# select * from json_each(fooj(3,4));
 key │ value
─────┼───────
 a   │ 3
 b   │ 4

... in the json api (which is rich and getting richer) can be used to
work around the anonymous row limitations.

merlin

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

From

Jim Nasby

Date:

03 September 2016, 02:55:41

On 8/29/16 6:28 AM, Tom Lane wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
>> > 2016-08-29 1:59 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
>>> >> It would be nice if there was a way to pass dynamically formed records
>>> >> around, similar to how you can pass the results of row() around. Someone
>>> >> else has actually be asking about this at https://github.com/decibel/pg_
>>> >> lambda/issues/1.
>> > Probably there is a space to be PLpgSQL more flexible - but there are
>> > limits - PLpgSQL is black box for SQL engine, and when output is any record
>> > type, then SQL engine knows zero about returning data structure in
>> > preprocessing time.
> Exactly.  You can pass anonymous record types around today, as long as you
> don't do anything that requires knowing what their contents are, either in
> the function or in the calling query:

What I was thinking of is something (like a function) that has
explicitly defined what the contents of the record are.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

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

From

Merlin Moncure

Date:

07 September 2016, 22:03:02

 Fri, Sep 2, 2016 at 6:55 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:
> On 8/29/16 6:28 AM, Tom Lane wrote:
>>
>> Pavel Stehule <pavel.stehule@gmail.com> writes:
>>>
>>> > 2016-08-29 1:59 GMT+02:00 Jim Nasby <Jim.Nasby@bluetreble.com>:
>>>>
>>>> >> It would be nice if there was a way to pass dynamically formed
>>>> >> records
>>>> >> around, similar to how you can pass the results of row() around.
>>>> >> Someone
>>>> >> else has actually be asking about this at
>>>> >> https://github.com/decibel/pg_
>>>> >> lambda/issues/1.
>>>
>>> > Probably there is a space to be PLpgSQL more flexible - but there are
>>> > limits - PLpgSQL is black box for SQL engine, and when output is any
>>> > record
>>> > type, then SQL engine knows zero about returning data structure in
>>> > preprocessing time.
>>
>> Exactly.  You can pass anonymous record types around today, as long as you
>> don't do anything that requires knowing what their contents are, either in
>> the function or in the calling query:
>
> What I was thinking of is something (like a function) that has explicitly
> defined what the contents of the record are.

We have that already, it's named 'json_each_text' :-).

merlin

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

From

Jim Nasby

Date:

08 September 2016, 01:27:33

On 9/7/16 2:02 PM, Merlin Moncure wrote:
>>> >> Exactly.  You can pass anonymous record types around today, as long as you
>>> >> don't do anything that requires knowing what their contents are, either in
>>> >> the function or in the calling query:
>> >
>> > What I was thinking of is something (like a function) that has explicitly
>> > defined what the contents of the record are.
> We have that already, it's named 'json_each_text'

Apparently you haven't looked at json parse/deparse costs ;P
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461

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

From

Tom Lane

Date:

08 September 2016, 01:39:34

Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 9/7/16 2:02 PM, Merlin Moncure wrote:
>> We have that already, it's named 'json_each_text'

> Apparently you haven't looked at json parse/deparse costs ;P

Well, a PL function is gonna be none too cheap either.

Using something like JSON definitely has lots to recommend it --- eg, it
probably won't break when you find out your initial spec for the transport
format was too simplistic.

            regards, tom lane