Thread: 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