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
