From: Tom Lane <tgl@sss.pgh.pa.us>
To: Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE>
Cc: pgsql-sql@postgresql.org
Subject: Re: Calling stored procedures in table constraint checks
Date: Mon, 12 Aug 2002 11:11:07 -0400
Message-ID: <5165.1029165067@sss.pgh.pa.us> (raw)
In-Reply-To: <871y9417yi.fsf@CERT.Uni-Stuttgart.DE>
References: <871y9417yi.fsf@CERT.Uni-Stuttgart.DE>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, Weimer@CERT.Uni-Stuttgart.DE
Subject: Re: Calling stored procedures in table constraint checks
In-Reply-To: <5165.1029165067@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox