Thread: Row type select syntax - inconsistency or misunderstanding?

(PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.2
20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu9))

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:

    test=# select row(*) from thing;
    ERROR:  syntax error at or near "*" at character 12
    LINE 1: select row(*) from thing
                       ^
    test=# select row(thing.*) from thing;
           row
    ------------------
     ("(1,aardvark)")
    (1 row)

    test=# select row(id, name) from thing;
         row
    --------------
     (1,aardvark)
    (1 row)

    test=# select row((id, name)) from thing;
           row
    ------------------
     ("(1,aardvark)")
    (1 row)


1. It seems that this may be an inconsistency in how Postgres is returning
rowtypes.  row(thing.*) is behaving like row((col1, col2)), and row(*) just
breaks.  I understand why the double-parens in the last select do what
they do; the others puzzle me.

2. As a workaround, perhaps I could "unrow" the double-rowed version returned
by row(thing.*) or row ((col1,col2)).  However, I did not see a function listed
in the manual for this purpose.  Is there such a function?

3. My end goal is to make audit tables by having the audit tables store
a column of composite type (namely, the row type of the table being
audited).

Here's an example schema where I want to audit the "thing" table by dropping a
thing rowtype directly into the thing_audit table:

    CREATE TABLE thing (
        id integer NOT NULL,
        name text
    );
    CREATE TABLE thing_audit (
        audit_id integer NOT NULL,
        thing_row thing
    );
    INSERT INTO thing (id, name) VALUES (1, 'aardvark');

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;

However, the behavior of rows doesn't seem to play nice.  It seems like
I could do this with an exhaustive listing of columns in my audited
table, but that seems kludgey.

Any thoughts on either the "unrow" function workaround or my end goal?

Best,

Randall

--
Randall Lucas       Tercent, Inc.       DF93EAD1

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

On Wed, Aug 23, 2006 at 02:31:35PM -0400, Tom Lane wrote:
> 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 :-(

If one is willing to construct an expression (for example, for use in
an EXECUTE within a plpgsql function), the following works in 8.1.3:

    create or replace function audit_table_thing(int) returns int as $$
    declare
    in_id alias for $1;
    record_text text;
    begin
    select into record_text quote_literal(textin(record_out(thing.*))) from thing where id=in_id;
    raise notice '%', record_text;
    execute 'insert into thing_audit (audit_id, thing_row) values (' || in_id || ', cast(' || record_text || ' as
thing))'; 
    return 1;
    end
    $$ language 'plpgsql';

Is it safe to say that the row functions are bleeding edge?

Randall

--
Randall Lucas       Tercent, Inc.       DF93EAD1