(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

Search Discussions

Discussion Posts

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 1 of 3 | next ›
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 23, '06 at 5:38p
activeAug 23, '06 at 6:43p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Randall Lucas: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase