Thread: SELECT syntax synopsis: column_definition?

Can any one give an example of the difference between a column_alias and a column_definition when
using a function in the FROM clause?

from the manual:
http://www.postgresql.org/docs/8.2/interactive/sql-select.html

"function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition
[, ...] ) ]"

Regards,
Richard Broersma Jr.

On Aug 21, 2007, at 14:34 , Richard Broersma Jr wrote:

> Can any one give an example of the difference between a  
> column_alias and a column_definition when
> using a function in the FROM clause?
>
> from the manual:
> http://www.postgresql.org/docs/8.2/interactive/sql-select.html
>
> "function_name ( [ argument [, ...] ] ) [ AS ] alias  
> [ ( column_alias [, ...] | column_definition
> [, ...] ) ]"

I believe it's similar to this

# select * from generate_series(1,10) as a(s);
s
----  1  2  3  4  5  6  7  8  9
10
(10 rows)

But like this:

# select * from generate_series(1,10) as a(s text);

But not quite, as this raises an error :)

ERROR:  a column definition list is only allowed for functions  
returning "record"

So the *form* is right, but I don't know of an example that works.

You've got me curious now, too!

Michael Glaesemann
grzm seespotcode net

On Aug 21, 2007, at 18:04 , Michael Glaesemann wrote:

> So the *form* is right, but I don't know of an example that works.

CREATE TABLE foos
(    foo text PRIMARY KEY    , title text NOT NULL
);

INSERT INTO foos (foo, title) values
('foo', 'the great')
, ('bar', 'the extravagant')
, ('baz', 'the indisputable');

CREATE OR REPLACE FUNCTION get_foo() RETURNS record
LANGUAGE plpgsql AS $body$
DECLARE  v_record record;
BEGIN    SELECT INTO v_record        *    FROM foos    ORDER BY RANDOM()    LIMIT 1;    RETURN v_record;
END;
$body$;
  a  |        b
-----+------------------
baz | the indisputable
(1 row)

test=#      SELECT *
test-# FROM get_foo() AS (a text, b text);  a  |        b
-----+-----------------
bar | the extravagant
(1 row)

IIRC, this form is used by the crosstab functions in tablefunc.

Michael Glaesemann
grzm seespotcode net

"Michael Glaesemann" <grzm@seespotcode.net> writes:

> ERROR:  a column definition list is only allowed for functions  returning
> "record"
>
> So the *form* is right, but I don't know of an example that works.

postgres=# create function testf() returns record as 'select 1' language sql;
CREATE FUNCTION
postgres=# select * from testf() as (i integer);i 
---1
(1 row)


I haven't quite figured out how this is useful though. It probably makes more
sense if you use plpgsql but I still don't quite see what the use case is.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com

On 8/21/07, Gregory Stark <stark@enterprisedb.com> wrote:
> "Michael Glaesemann" <grzm@seespotcode.net> writes:
>
> > ERROR:  a column definition list is only allowed for functions  returning
> > "record"
> >
> > So the *form* is right, but I don't know of an example that works.
>
> postgres=# create function testf() returns record as 'select 1' language sql;
> CREATE FUNCTION
> postgres=# select * from testf() as (i integer);
>  i
> ---
>  1
> (1 row)
>
>
> I haven't quite figured out how this is useful though. It probably makes more
> sense if you use plpgsql but I still don't quite see what the use case is.

I use them with the crosstab function in the tablefunc contrib module.

Gregory Stark wrote:
> "Michael Glaesemann" <grzm@seespotcode.net> writes:
> 
> > ERROR:  a column definition list is only allowed for functions  returning
> > "record"
> >
> > So the *form* is right, but I don't know of an example that works.
> 
> postgres=# create function testf() returns record as 'select 1' language sql;
> CREATE FUNCTION
> postgres=# select * from testf() as (i integer);
>  i 
> ---
>  1
> (1 row)
> 
> 
> I haven't quite figured out how this is useful though. It probably makes more
> sense if you use plpgsql but I still don't quite see what the use case is.

Any function declared as returning SETOF RECORD needs it, when you don't
use OUT params.  Before OUT params existed, it was the only way to use
those functions.

-- 
Alvaro Herrera       Valdivia, Chile   ICBM: S 39º 49' 18.1", W 73º 13' 56.4"
"The eagle never lost so much time, as
when he submitted to learn of the crow." (William Blake)

--- Alvaro Herrera <alvherre@commandprompt.com> wrote:
> Any function declared as returning SETOF RECORD needs it, when you don't
> use OUT params.  Before OUT params existed, it was the only way to use
> those functions.

Thanks everyone for the exposition!  It makes sense.

Regards,
Richard Broersma Jr.

Gregory Stark <stark@enterprisedb.com> writes:
> I haven't quite figured out how this is useful though. It probably makes more
> sense if you use plpgsql but I still don't quite see what the use case is.

IIRC, the case that actually convinced people to allow it was dblink.
You want to be able to do something like

select *
from dblink('select a,b,c from remote_table') as (a int, b text, c float8);

The declaration of dblink can't be any more specific than "RETURNS SETOF
RECORD", so there's no help to be had there.  The only way to explain to
the parser what your dblink call is going to return is something like
the above.  And the parser does need to know it, so it knows what to
expand "*" to (or more generally, to do things like joins involving the
rowset result).
        regards, tom lane