Re: Row type select syntax - inconsistency or misunderstanding?

Randall Lucas <rlucas(at)tercent(dot)com> writes:
> In trying to retrieve a row as a composite rowtype from a table, I'm running
> into what appears to be an inconsistent result based on whether I select *,
> table.*, or the list of columns in the table:

FWIW, we've changed the behavior of ROW(foo.*) for 8.2 --- it now
behaves as if you'd written out all the columns of foo explicitly.
I don't have a solution for you in 8.1 short of writing 'em all out :-(

> I'd like to be able to say something like:
> INSERT INTO thing_audit (id, thing_row)
> SELECT 101, ROW(thing.*) FROM thing WHERE id=1;

In CVS HEAD this seems to work except you have to explicitly cast the
ROW constructor:

regression=# select * from int8_tbl;
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)

regression=# create table a_int8_tbl(id int, data int8_tbl);
CREATE TABLE
regression=# insert into a_int8_tbl select 101,row(int8_tbl.*) from int8_tbl;
ERROR: cannot cast type record to int8_tbl
regression=# insert into a_int8_tbl select 101,row(int8_tbl.*)::int8_tbl from int8_tbl;
INSERT 0 5
regression=# select * from a_int8_tbl;
id | data
-----+--------------------------------------
101 | (123,456)
101 | (123,4567890123456789)
101 | (4567890123456789,123)
101 | (4567890123456789,4567890123456789)
101 | (4567890123456789,-4567890123456789)
(5 rows)

regression=#

I don't remember at the moment why we insist on the explicit cast.

regards, tom lane