Search Postgresql Archives

How to insert into 2 tables from a view?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

I am having a problem trying to figure out.  

I have two tables behind a view and am trying to figure out how to create the correct insert rule so that inserting into the view is redirected to the two tables.  I thought I had is solved using a stored procedure, but doing an insert into view ... returning id causes the insert to fail with this error:

ERROR:  cannot perform INSERT RETURNING on relation "orig_view"
HINT:  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause

We are running pg 9.0 and I think this version of PG is the bottleneck to getting this done.  Does anyone know how to get around it?  Below is a basic example demonstrating what we are wanting to do.

CREATE TABLE table1 (
  table1_id SERIAL PRIMARY KEY,
  table1_field1 TEXT
);

CREATE TABLE table2 (
  table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) ON DELETE CASCADE,
  table2_field1 TEXT
);

CREATE VIEW orig_table AS
    SELECT table1_id, table1_field_1, table2_field1
      FROM table1
      JOIN table2 USING (table1_id);

CREATE FUNCTION orig_table_insert(in_table1_id integer, in_table1_field1 text, in_table2_field1 text)
    RETURNS SETOF orig_table
    LANGUAGE plpgsql
    AS
    $BODY$
    DECLARE
        v_table1_id table1.table1_id%TYPE
    BEGIN
        INSERT INTO table1 (
            table1_id, table1_field1
        ) VALUES (
            in_table1_id, in_table1_field1
        )   
        RETURNING table1_id
        INTO v_table1_id;

        INSERT INTO table2 (
            table1_id, table2_field1
        ) VALUES (
            v_table_id, in_table2_field1
        );

        RETURN QUERY SELECT table1_id, table1_field1, table2_field1
                       FROM orig_table
                      WHERE table1_id = v_table1_id;

    END;
    $BODY$;

    
CREATE RULE orig_table_insert_rule AS
    ON INSERT
    TO orig_table
    DO INSTEAD
       SELECT orig_table_insert(NEW.table1_id, NEW.table1_field1, NEW.table2_field1);

Thanks,

Chris

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux