To: Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>
Cc: pgsql-sql@postgresql.org
Subject: Re: Calling stored procedures in table constraint checks 
In-reply-to: <871y9417yi.fsf@CERT.Uni-Stuttgart.DE> 
References: <871y9417yi.fsf@CERT.Uni-Stuttgart.DE>
Comments: In-reply-to Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>
	message dated "Mon, 12 Aug 2002 16:13:09 +0200"
Date: Mon, 12 Aug 2002 11:11:07 -0400
Message-ID: <5165.1029165067@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE> writes:
> I guess I need an example how I can pass an entire row to a stored
> procedure called in a table constraint check.
> Is this possible at all?

In CVS tip it works to do this:

regression=# create function foo(tenk1) returns int as '
regression'# begin
regression'# return $1.unique2;
regression'# end' language plpgsql;
CREATE
-- min(unique2) is 0, so:
regression=# alter table tenk1 add constraint c2 check (foo(tenk1.*) > 0);
ERROR:  AlterTableAddConstraint: rejected due to CHECK constraint c2
regression=# alter table tenk1 add constraint c2 check (foo(tenk1.*) >= 0);
ALTER TABLE

The older syntax also works:

regression=# alter table tenk1 add constraint c3 check (foo(tenk1) >= 0);
ALTER TABLE

The latter *ought* to work in 7.2, but seems not to --- it looks like
the thing runs through the ALTER TABLE check, and then fails at the last
moment where it's trying to re-parse the expression for storage.
Grumble.

In any case this is a bit of a mess, because you can't create the
function until the row type exists, so you have to do it as create
table, create function, alter table add constraint.  That's not only
ugly but will confuse the heck out of pg_dump.  (thinks...)  It might
work better to create a parent table, create the function taking
the parent's rowtype, then define the table you care about as inheriting
from the parent with no added columns and having the desired constraint.

In any case you'll probably have to wait for 7.3.

			regards, tom lane