Search Postgresql Archives

Row type select syntax - inconsistency or misunderstanding?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



(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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux