Row type select syntax - inconsistency or misunderstanding?

Поиск
Список
Период
Сортировка
От Randall Lucas
Тема Row type select syntax - inconsistency or misunderstanding?
Дата
Msg-id 20060823173800.GS17085@ontology.tercent.com
обсуждение исходный текст
Ответы Re: Row type select syntax - inconsistency or misunderstanding?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
(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

В списке pgsql-general по дате отправления:

Предыдущее
От: "David Collrep"
Дата:
Сообщение: help with postgresql and stats
Следующее
От: "Silvela, Jaime \(Exchange\)"
Дата:
Сообщение: CASCADING could not open relation with OID