X-Original-To: pgsql-interfaces-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D9489DA188 for ; Thu, 22 Sep 2005 00:38:56 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 13762-06 for ; Thu, 22 Sep 2005 03:38:54 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id BCACDDA096 for ; Thu, 22 Sep 2005 00:38:52 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j8M3cmT2056244 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Wed, 21 Sep 2005 21:38:50 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j8M3cmhD090485; Wed, 21 Sep 2005 21:38:48 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j8M3cl6T090484; Wed, 21 Sep 2005 21:38:47 -0600 (MDT) (envelope-from mfuhr) Date: Wed, 21 Sep 2005 21:38:47 -0600 From: Michael Fuhr To: Robert Wimmer Cc: pgsql-interfaces@postgresql.org Subject: Re: check foreign constraints before delete Message-ID: <20050922033847.GB90411@winnie.fuhr.org> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.109 required=5 tests=[AWL=-0.098, UPPERCASE_25_50=0.207] X-Spam-Level: X-Archive-Number: 200509/32 X-Sequence-Number: 5755 On Wed, Sep 21, 2005 at 11:43:23PM +0000, Robert Wimmer wrote: > i want to write a function in plpgsql, which checks a foreign key > constraint violation on deleting a special value in my table. for example > > CREATE TABLE to (n NAME PRIMARY KEY); > CREATE TABLE t1 (n_name NAME REFERENCES to(n), id INTEGER ...); > > CREATE FUNCTION check_before_delete(..some value ...) RETURNS INTEGER AS ' > .. > IF foreign_key_violation THEN RETURN -1 END IF; > RETURN 0; > .. > END; In PostgreSQL 8.0 you could use an EXCEPTION clause: http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING -- Michael Fuhr