Re: Bug for view with rules - Mailing list pgadmin-support

-----Original Message-----
From: Donald Fraser [mailto:demolish@cwgsy.net]
Sent: 23 December 2002 14:22
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Bug for view with rules

Hi Dave,

thanks for the latest fix.

 

Have spent all morning creating the situation for a view that causes a crash - here it is...

 

First create a simple table such as:

 

CREATE

TABLE public.tbl_catchbug (

id int4,

s_sometext text

) WITHOUT OIDS;

GRANT

ALL ON TABLE public.tbl_catchbug TO PUBLIC;

GRANT

ALL ON TABLE public.tbl_catchbug TO postgres;

 

Next create a view for the table such as:

 

CREATE

VIEW public.vu_tbl_catchbug AS SELECT t.id, t.s_sometext FROM tbl_catchbug AS t;

GRANT

INSERT, SELECT, UPDATE ON TABLE public.vu_tbl_catchbug TO PUBLIC;

GRANT

ALL ON TABLE public.vu_tbl_catchbug TO postgres;

 

Next create a function in PL/SQL such as:

 

CREATE

FUNCTION raise_exception1(text) RETURNS int4 AS '

DECLARE s_message ALIAS FOR $1;

BEGIN

    RAISE EXCEPTION s_message;

    RETURN -1;

END; '

LANGUAGE 'plpgsql';

 

Next create the following rules for the view:

 

CREATE

RULE rul_vu_tbl_catchbug_i1 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL AND s_sometext IS NULL) DO (SELECT raise_exception1('You must supply some data'));

CREATE

RULE rul_vu_tbl_catchbug_i2 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NULL) DO (INSERT INTO tbl_catchbug (id, s_sometext) VALUES('1', NEW.s_sometext));

CREATE

RULE rul_vu_tbl_catchbug_i3 AS ON INSERT TO vu_tbl_catchbug WHERE (id IS NOT NULL) DO (SELECT raise_exception1('This is an exception'));

CREATE

RULE rul_vu_tbl_catchbug_i4 AS ON INSERT TO vu_tbl_catchbug DO INSTEAD NOTHING;

 

Finally do an insert into the view with the following SQL statement:

 

INSERT INTO vu_tbl_catchbug (s_sometext) VALUES('This should crash pgAdminII');

 

That should do the trick...

 

I'm off for the rest of the festive season (back to work on the 2nd),

hope you have a good one too,

regards,

Donald.