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

On Fri, Aug 26, 2016 at 10:50 AM, Cachique <cachique(at)gmail(dot)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