Thread: check foreign constraints before delete

hi,

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;
...

so i can avoid the database errormessages.

i checked some 'd'  commands with the 'E' option. but i have no idea for a 
smart solution of this problem

thanx in advance (tom?)

sepp

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