Thread

1. Calling stored procedures in table constraint checks

   Florian Weimer <weimer@cert.uni-stuttgart.de> — 2002-08-12T14:13:09Z

   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?
   
   -- 
   Florian Weimer 	                  Weimer@CERT.Uni-Stuttgart.DE
   University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
   RUS-CERT                          fax +49-711-685-5898
   
   

2. Re: Calling stored procedures in table constraint checks

   Tom Lane <tgl@sss.pgh.pa.us> — 2002-08-12T15:11:07Z

   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