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 THEN
RAISE 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(at)mjtsa(dot)com]
> Sent: 27 February 2001 17:43
> To: pgsql-sql(at)postgresql(dot)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(at)postgresql(dot)org