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/i...ql-select.html

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

Regards,
Richard Broersma Jr.

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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/i...ql-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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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)

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

--- 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.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend