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'''',
''''[EMAIL PROTECTED]'''', ''''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 [EMAIL PROTECTED])
