Re: Row type select syntax - inconsistency or misunderstanding? - Mailing list pgsql-general

Randall Lucas <rlucas@tercent.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