Sorry, forgot the version information: 7.3.2

Full schema and example:


CREATE TABLE owners_admin (
owner_id integer DEFAULT nextval('"owners_owner_id_seq"'::text) NOT
NULL,
first_name character varying(15) DEFAULT '',
last_name character varying(15) DEFAULT '',
login character varying(12) NOT NULL,
title text DEFAULT '',
email character varying(35) DEFAULT '',
time_added timestamp with time zone DEFAULT ('now'::text)::timestamp(6)
with time zone,
admin boolean DEFAULT false,
disabled integer DEFAULT '0',
manager integer
);

CREATE TABLE owners_user (
owner_id integer,
expertise text,
home_form character varying(20),
form_count integer,
cal_min_hr smallint,
cal_max_hr smallint,
query_max integer
);



CREATE VIEW owners
AS
SELECT a.owner_id, a.first_name, a.last_name, a.login, a.title,
u.expertise,
a.email, a.time_added, u.home_form, u.form_count, u.cal_min_hr,
u.cal_max_hr, a.admin, a.disabled, u.query_max, a.manager
FROM owners_user u, owners_admin a
WHERE a.owner_id = u.owner_id;

CREATE RULE view_insert AS ON INSERT TO owners
DO INSTEAD
(
INSERT INTO owners_admin
(first_name, last_name, login, title, email, time_added, admin,
disabled)
VALUES
(COALESCE(new.first_name, ''), COALESCE(new.last_name, ''),
new.login, COALESCE(new.title, ''), COALESCE(new.email, ''),
CURRENT_TIMESTAMP, COALESCE(new.admin, FALSE),
COALESCE(new.disabled, '0'));
INSERT INTO owners_user
(owner_id, expertise, home_form, form_count, cal_min_hr,
cal_max_hr, query_max)
VALUES
((SELECT owner_id FROM owners_admin WHERE login = new.login),
new.expertise, new.home_form, new.form_count,
COALESCE(new.cal_min_hr, '9'), COALESCE(new.cal_max_hr,
'16'),
new.query_max)
);

CREATE OR REPLACE FUNCTION test_view()
RETURNS INT AS '
DECLARE
BEGIN
EXECUTE ''INSERT INTO owners (first_name, last_name, email,
login, title, expertise)
VALUES
(''''Test'''',''''Person'''',
''''Test (AT) mydomain (DOT) test'''', ''''test'''',
''''Tester'''', ''''Testing'''')'';
END;
' LANGUAGE PLPGSQL;

When you call test_view() you get:

Error is:
FATAL: SPI: improper call to spi_dest_setup
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.

Note that inserts work fine from the command line.

__________________________________________________ _______________
Need more e-mail storage? Get 10MB with Hotmail Extra Storage.
http://join.msn.com/?PAGE=features/es


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

"Christopher Travers" <einhverfr (AT) hotmail (DOT) com> writes:
Update to 7.3.4 --- I see this fix in the CVS logs:

2003-02-14 16:12 tgl

* src/backend/executor/spi.c (REL7_3_STABLE): Fix SPI result logic
for case where there are multiple statements of the same type in a
rule. Per bug report from Pavel Hanak.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

My bad;

As Tom has pointed out this is a known issue. Thanks. I wonder why my
first search didn't find it (probably a typo on my part)

Best Wishes.

__________________________________________________ _______________
Try MSN Messenger 6.0 with integrated webcam functionality!
http://www.msnmessenger-download.com...g/reach_webcam


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html