Thread: Re: BUG: PLPGSQL function causes PgSQL process to die when inserting into

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

"Christopher Travers" <einhverfr@hotmail.com> writes:
> Sorry, forgot the version information: 7.3.2

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