pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Help creating rules/triggers/functions
5+ messages / 4 participants
[nested] [flat]

* Help creating rules/triggers/functions
@ 2001-02-27 16:43  Blaise Carrupt <bc@mjtsa.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Blaise Carrupt @ 2001-02-27 16:43 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: Help creating rules/triggers/functions
@ 2001-03-02 14:09  Sondaar Roelof <roelof.sondaar@scania.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Sondaar Roelof @ 2001-03-02 14:09 UTC (permalink / raw)
  To: 'Blaise Carrupt' <bc@mjtsa.com>; pgsql-sql

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@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



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Help creating rules/triggers/functions
@ 2001-03-02 15:34  Jan Wieck <janwieck@Yahoo.com>
  parent: Blaise Carrupt <bc@mjtsa.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Jan Wieck @ 2001-03-02 15:34 UTC (permalink / raw)
  To: Blaise Carrupt <bc@mjtsa.com>; +Cc: pgsql-sql

Blaise Carrupt wrote:
> 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 FUNCTION A_del () RETURNS opaque AS '
        DECLARE
            nrefs integer;
        BEGIN
            nrefs := count(*) FROM b WHERE a_id = OLD.i_id;
            IF nrefs > 0 THEN
                RAISE EXCEPTION ''a_id % still referenced from b'', OLD.i_id;
            END IF;
            RETURN OLD;
        END;'
        LANGUAGE 'plpgsql';
>
> create trigger before delete from A for each row execute procedure A_del(old.id)

        CREATE TRIGGER A_del BEFORE DELETE ON A
            FOR EACH ROW EXECUTE PROCEDURE A_del();

>
>
> 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 ?

    Alternatively  (IMHO  preferred)  you could use a referential
    integrity constraint in  table  B,  which  would  also  cover
    UPDATE on A and check values inserted/updated into/in B.

        CREATE TABLE B ( ...
            FOREIGN KEY (i_id) REFERENCES A (a_id)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Help creating rules/triggers/functions
@ 2001-03-02 17:05  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Blaise Carrupt <bc@mjtsa.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Stephan Szabo @ 2001-03-02 17:05 UTC (permalink / raw)
  To: Blaise Carrupt <bc@mjtsa.com>; +Cc: pgsql-sql


If you're only doing a simple check for reference, why not use
foreign keys?

In general however, you probably want to use plpgsql to define the
trigger.  And trigger functions don't take parameters in the normal 
sense, the function should be created taking no args and returning
opaque; the parameters you add in create trigger are passed in
TG_ARGS (i believe).  You might want to look at the user and programmer
guides for more information on trigger functions.

On Tue, 27 Feb 2001, Blaise Carrupt wrote:

> 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 ?





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: Help creating rules/triggers/functions
@ 2001-03-05 08:39  Sondaar Roelof <roelof.sondaar@scania.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Sondaar Roelof @ 2001-03-05 08:39 UTC (permalink / raw)
  To: 'Blaise Carrupt' <bc@mjtsa.com>; +Cc: pgsql-sql

Hello Blaise,

I included a script I used to build/maintain a database.
It also includes triggers and history logging.
I hope you can use it.
In the DO NOT use this part are test things which might not be correct.

Best regards,
Roelof
 <<DBcreate.scania.02.sql>> 

> -----Original Message-----
> From:	Blaise Carrupt [SMTP:bc@mjtsa.com]
> Sent:	02 March 2001 16:29
> To:	roelof.sondaar@scania.com
> Subject:	RE: [SQL] Help creating rules/triggers/functions
> 
> Hi Roelof !
> 
> I thank you for your answer. It allowed me to find a way to resolve my
> problems. 
> What is missing to your answer is the trigger. In the documentation, I
> found a 
> way doing it with a C procedure. But I was sure Postgres could do it in a 
> simpler way.
> 
> If it interests you, what I got now is :
> 
> DROP FUNCTION a_del();
> 
> CREATE FUNCTION a_del() RETURNS OPAQUE AS
> 'DECLARE
>     id INT4;
>  BEGIN
>     SELECT a_id INTO id			/* I'm not sure INTO is
> mandatory... */
>        FROM a
>        WHERE a_id = OLD.addr_id;
>        
>     IF FOUND THEN
>         RAISE EXCEPTION ''not allowed !'';
>     END IF;
>     
>     RETURN OLD;
>  END;'
> LANGUAGE 'plpgsql';
> 
> 
> 
> DROP TRIGGER a_del_trg ON a;
> 
> CREATE TRIGGER a_del_trg
> BEFORE DELETE ON a
> FOR EACH ROW
> EXECUTE PROCEDURE a_del();
> 
> 
> It's much more complicate than Ingres, but it works as well. I don't know
> how 
> a_del knows OLD...
> 
> I thank you again for your help.
> 
> 
> ___________________
> B. Carrupt
>        
>        
>    

Attachments:

  DBcreate.scania.02.sql (application/octet-stream, 12.3K) download

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2001-03-05 08:39 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-02-27 16:43 Help creating rules/triggers/functions Blaise Carrupt <bc@mjtsa.com>
2001-03-02 15:34 ` Jan Wieck <janwieck@Yahoo.com>
2001-03-02 17:05 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-03-02 14:09 RE: Help creating rules/triggers/functions Sondaar Roelof <roelof.sondaar@scania.com>
2001-03-05 08:39 RE: Help creating rules/triggers/functions Sondaar Roelof <roelof.sondaar@scania.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox