RE: Help creating rules/triggers/functions

Hello Blaise,

The following is how I tested your question;
DROP SEQUENCE b_id_seq;
DROP TABLE b;
CREATE TABLE b (id             SERIAL,description     TEXT
);
INSERT INTO b (description) VALUES('aaaaa');
INSERT INTO b (description) VALUES('bbbbb');
SELECT * FROM b;

DROP FUNCTION a_del(int4);
CREATE FUNCTION a_del(int4)
RETURNS int4
AS 'DECLARE
BEGIN   IF (SELECT count(*) FROM b WHERE id = $1) > 0 THENRAISE EXCEPTION ''not allowed !'';  end if;  RETURN $1;
END;'
LANGUAGE 'plpgsql';

SELECT A_del(45);
SELECT A_del(1);

The answer is:dhcp=# SELECT a_del(45);a_del
-------   45
(1 row)

dhcp=# SELECT a_del(1);
ERROR:  not allowed !

I hope this helps.

Best regrards,
Roelof


> -----Original Message-----
> From:    Blaise Carrupt [SMTP:bc@mjtsa.com]
> Sent:    27 February 2001 17:43
> To:    pgsql-sql@postgresql.org
> Subject:    [SQL] Help creating rules/triggers/functions
> 
> Hi all !
> 
> I use PostgreSQL 7.0.2 on a HP-UX system.
> 
> I would like to create a simple function and a simple trigger (or rule)
> that 
> deny a delete from a table if the row is referenced in another table.
> 
> I though it should look like this (from my Ingres experience... :) :
> 
> create function A_del(int4 i_id)
> BEGIN
>    SELECT id
>      FROM b
>      where a_id = :i_id;
>      
>    if rowcount > 0 then
>     RAISE EXCEPTION "not allowed !"
>    end if;
> END
> 
> 
> create trigger before delete from A for each row execute procedure
> A_del(old.id)
> 
> 
> But it seems to be much more complicated with Postgres (create a C
> function 
> using CurrentTriggerData,...). May I have missed something or is it really
> much 
> more complicated ?
> 
> Thanks for help.
> 
> _____________
> B. Carrupt
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org