SELECT table_type FROM table;
By accident, a colleague came across something unexpected. Here is a
simple example:
create table testo(gonk integer,spoodle text);
CREATE TABLE
agentm=# insert into testo values(1,'hello');
INSERT 0 1
agentm=# insert into testo values(2,'text');
INSERT 0 1
agentm=# select testo from testo;
testo
-----------
(1,hello)
(2,text)
(2 rows)
Obviously, this is intentional behavior but where is it documented? I am
aware that testo is also a type and that a set is returned for each row
but this must be a special case, no? Alternate types don't seem to apply.
agentm=# create type nice as (gonk integer,spoodle text);
CREATE TYPE
agentm=# select nice from testo;
ERROR: column "nice" does not exist
agentm=# select *::nice from testo;
ERROR: syntax error at or near "::" at character 9
LINE 1: select *::nice from testo;
^
agentm=# select cast(* as nice) from testo;
ERROR: syntax error at or near "*" at character 13
LINE 1: select cast(* as nice) from testo;
^
Also, how can I turn each set row into an array?
-M
"A.M." <agentm@themactionfaction.com> writes:
agentm=# select testo from testo;
testo
-----------
(1,hello)
(2,text)
(2 rows)
Obviously, this is intentional behavior but where is it documented?
Well, it's mentioned in passing in section 32.4.2 "SQL Functions on
Composite Types",
http://www.postgresql.org/docs/8.1/static/xfunc-sql.html#AEN31648
where it says "The table row can alternatively be referenced using just
the table name". Personally I prefer the syntax "table.*"; the syntax
without * is a holdover from PostQUEL IIRC.
agentm=# select *::nice from testo;
ERROR: syntax error at or near "::" at character 9
The syntactically right thing would be
regression=# select testo::nice from testo;
ERROR: cannot cast type testo to nice
or
regression=# select (testo.*)::nice from testo;
ERROR: cannot cast type testo to nice
We don't have any automatic support for casts from one composite type to
another, but you can add your own:
regression=# create function nice(testo) returns nice language sql as $$
regression$# select $1.* $$ strict immutable;
CREATE FUNCTION
regression=# create cast(testo as nice) with function nice(testo);
CREATE CAST
regression=# select (testo.*)::nice from testo;
testo
-----------
(1,hello)
(2,text)
(2 rows)
regards, tom lane