pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT syntax synopsis: column_definition?
8+ messages / 6 participants
[nested] [flat]
* SELECT syntax synopsis: column_definition?
@ 2007-08-21 19:34 Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
0 siblings, 1 reply; 8+ messages in thread
From: Richard Broersma Jr @ 2007-08-21 19:34 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT syntax synopsis: column_definition?
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
@ 2007-08-21 23:04 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:17 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:53 ` Re: SELECT syntax synopsis: column_definition? Gregory Stark <stark@enterprisedb.com>
0 siblings, 2 replies; 8+ messages in thread
From: Michael Glaesemann @ 2007-08-21 23:04 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT syntax synopsis: column_definition?
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
@ 2007-08-21 23:17 ` Michael Glaesemann <grzm@seespotcode.net>
1 sibling, 0 replies; 8+ messages in thread
From: Michael Glaesemann @ 2007-08-21 23:17 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT syntax synopsis: column_definition?
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
@ 2007-08-21 23:53 ` Gregory Stark <stark@enterprisedb.com>
2007-08-22 01:42 ` Re: SELECT syntax synopsis: column_definition? Scott Marlowe <scott.marlowe@gmail.com>
2007-08-22 01:55 ` Re: SELECT syntax synopsis: column_definition? Alvaro Herrera <alvherre@commandprompt.com>
2007-08-22 05:33 ` Re: SELECT syntax synopsis: column_definition? Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 3 replies; 8+ messages in thread
From: Gregory Stark @ 2007-08-21 23:53 UTC (permalink / raw)
To: Michael Glaesemann <grzm@seespotcode.net>; +Cc: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
"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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT syntax synopsis: column_definition?
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:53 ` Re: SELECT syntax synopsis: column_definition? Gregory Stark <stark@enterprisedb.com>
@ 2007-08-22 01:42 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2007-08-22 01:42 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT syntax synopsis: column_definition?
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:53 ` Re: SELECT syntax synopsis: column_definition? Gregory Stark <stark@enterprisedb.com>
@ 2007-08-22 01:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-08-22 03:10 ` Re: SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2007-08-22 01:55 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
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)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT syntax synopsis: column_definition?
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:53 ` Re: SELECT syntax synopsis: column_definition? Gregory Stark <stark@enterprisedb.com>
2007-08-22 01:55 ` Re: SELECT syntax synopsis: column_definition? Alvaro Herrera <alvherre@commandprompt.com>
@ 2007-08-22 03:10 ` Richard Broersma Jr <rabroersma@yahoo.com>
0 siblings, 0 replies; 8+ messages in thread
From: Richard Broersma Jr @ 2007-08-22 03:10 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@commandprompt.com>; Gregory Stark <stark@enterprisedb.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
--- 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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: SELECT syntax synopsis: column_definition?
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Re: SELECT syntax synopsis: column_definition? Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:53 ` Re: SELECT syntax synopsis: column_definition? Gregory Stark <stark@enterprisedb.com>
@ 2007-08-22 05:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2007-08-22 05:33 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: Michael Glaesemann <grzm@seespotcode.net>; Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2007-08-22 05:33 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-21 19:34 SELECT syntax synopsis: column_definition? Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-21 23:04 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:17 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-21 23:53 ` Gregory Stark <stark@enterprisedb.com>
2007-08-22 01:42 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-08-22 01:55 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-08-22 03:10 ` Richard Broersma Jr <rabroersma@yahoo.com>
2007-08-22 05:33 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox