From: | "Dave Page" <dpage(at)vale-housing(dot)co(dot)uk> |
---|---|
To: | "Donald Fraser" <demolish(at)cwgsy(dot)net>, <pgadmin-support(at)postgresql(dot)org> |
Subject: | Re: Bug for view with rules |
Date: | 2002-12-23 20:22:51 |
Message-ID: | 03AF4E498C591348A42FC93DEA9661B8128DDC@mail.vale-housing.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-support |
Hi Donald,
I've tried this and it does indeed cause a crash. As suspected though,
it does appear to be ADO at fault, so I don't think there's a great deal
I can do about it I'm afraid.
Of course, pgAdmin III won't have this problem...
Regards, Dave.
-----Original Message-----
From: Donald Fraser [mailto:demolish(at)cwgsy(dot)net]
Sent: 23 December 2002 14:22
To: pgadmin-support(at)postgresql(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Reshat Sabiq | 2002-12-25 13:27:05 | int types migrated one level lower |
Previous Message | Dave Page | 2002-12-23 14:59:03 | Re: Bug for view with rules |