Skip site navigation (1) Skip section navigation (2)

Re: Resolution for "ERROR: cannot handle whole-row reference" ?

From: Sean Chittenden <sean(at)chittenden(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Resolution for "ERROR: cannot handle whole-row reference" ?
Date: 2004-03-28 19:59:25
Message-ID: 69B576C2-80F2-11D8-BCF7-000A95C705DC@chittenden.org (view raw or flat)
Thread:
Lists: pgsql-general
[snip function bits]

The first part I knew, but the historical behavior mentioned is 
interesting... I haven't run into a naming conflict yet, but will 
probably change things to preemptively thwart such problems.  This 
oddity seems pretty unknown and certainly not something I recall having 
read about... what are the odds that this will be changed in the 
future?  Low, very low, never, or someday if there's time/effort?

>> CREATE RULE t1_ins AS ON INSERT TO t1 DO INSTEAD SELECT f1(NEW);
>> ERROR:  function f1(t1) does not exist
>
> I am not sure any of this works for NEW or OLD references in rules,
> though, because they're not really tables.

This is my real problem though.  For example, I was doing something 
similar to:

-- v1 is a view of the table t1. I've got a few cases now where I've got
-- around 40 columns.
CREATE VIEW v1 AS SELECT c1, c2, c3, c4 FROM t1;
CREATE RULE v1_ins AS ON INSERT TO v1 DO INSTEAD SELECT t1_ins(NEW.c1, 
NEW.c2, NEW.c3, NEW.c4);

But then I've run into a few legitimate tables that have more than 
32cols and ran into the max function arg limit.  I was able to test and 
change t1_ins() to accept a table rowtype, but am now hung because I 
can't pass the NEW/OLD pseudorelational as a rowtype.  It's clearly a 
complex problem to have the rewrite engine handle this correctly in 
that I don't know how the database could resolve the NEW 
pseudorelational for an insert into v1 as a table rowtype for t1.  I'm 
lucky right now in that v1 is essentially defined as SELECT * FROM t1 
so the mapping seems intuitively easy, but that's deceptive.  I would, 
however, think that the NEW pseudorelational is structurally identical 
to a RECORD type in that a RECORD type has an unknown/dynamic form... 
which is ideally suited for my needs, but doesn't work.

Here's my creative - *ugly as sin*, should be shot for doing this, 
gagged I was laughing so hard while writing this evilness - workaround.

CREATE RULE v1_ins AS ON INSERT TO v1 DO INSTEAD (
	 SELECT check_or_create_tmp_table('t1');
	 SELECT t1_ins_1(NEW.c1, NEW.c2, NEW.c3, /* ... */ NEW.c32);
	 SELECT t1_ins_2(NEW.c33, NEW.c34, /* ... */ NEW.c42);
	 SELECT t1_ins_final());

check_or_create_tmp_table(TEXT) finds the table in $1 and creates a 
TEMP table mirror of $1, except the mirror accepts NULL values.  If it 
does exist, check_or_create_tmp_table() TRUNCATEs the TEMP TABLE.  
Then, t1_ins1_1() dumps all 32 of its args into the temp table and 
t1_ins_2() dumps does the same using an unqualified UPDATE (there's 
only one row in there anyway) to fill in the missing rows.  Repeat 
(#cols in table / FUNC_MAX_ARGS) + 1 times.  Finally, in 
t1_ins_final(), I scoop up the row in the temp table and 'INSERT INTO 
t1 SELECT * FROM t1_tmp'.

Scrub and repeat for UPDATE and DELETE RULEs.  Evil?  Absolutely.  
Inefficient?  Yup.  Does it work?  I'm embarrassed to say, but yes... 
sadly it does.  Is it brittle?  You bet!

Ideally I'd rather do CREATE FUNCTION t1_ins(RECORD) or whatever a RULE 
pseudorelational NEW/OLD could be passed as, then blindly do CREATE 
RULE v1_ins AS ON INSERT TO v1 DO INSTEAD SELECT t1_ins(NEW); and not 
have to worry about t1 or v1's structure changing.

Is there a less evil way of doing what I ended up doing for views with 
more than FUNC_MAX_ARGS cols?  -sc

-- 
Sean Chittenden


In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2004-03-28 21:13:33
Subject: Re: Index usage for BYTEA column in OR/IN clause
Previous:From: David GaramondDate: 2004-03-28 18:45:18
Subject: Re: Index usage for BYTEA column in OR/IN clause

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group