Postgres Partitioning with RETURNING on insert

gistfile1.sql

-- A method to have RETURNING work if you are partitioning data using trigger. 
-- The method to this madness is:
--
-- 1) Use the normal trigger mechanism to insert the data into the child tables, but
--    Instead of the trigger function returning NULL so that the row does not get⋅
--    inserted into the master table, it returns the row inserted into the child
--    table
--
-- 2) Postgres will insert the new row from the trigger into the master table
--
-- 3) Have an 'after insert' trigger on the master table that deletes from the⋅
--    master table with RETURNING.
--
-- This allows for the following type of statement to insert into the master table:
--⋅

INSERT INTO TABLE measurement( city_id, logdate, peaktemp, unitsales )⋅
           VALUES ( 42, 'today'::date, 12, 400 ) RETURNING *;

-- And the row will be partitioned into the appropriate child table

--- Master measurement table
CREATE TABLE measurement (
    id              int not null,
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);

-- Child partition tables
CREATE SEQUENCE measurement_y2007m02_seq start with 1 increment by 1 minvalue 1 cache 1;
CREATE TABLE measurement_y2007m02 (
    id int nextval('measurement_y2007m02_seq'::regclass),
    CHECK ( logdate >= DATE '2007-02-01' AND logdate < DATE '2007-03-01' )
) INHERITS (measurement);
CREATE SEQUENCE measurement_y2007m03_seq start with 1 increment by 1 minvalue 1 cache 1;
CREATE TABLE measurement_y2007m03 (
    id int nextval('measurement_y2007m03_seq'::regclass),
    CHECK ( logdate >= DATE '2007-03-01' AND logdate < DATE '2007-04-01'
    )   
) INHERITS (measurement);
...
CREATE SEQUENCE measurement_y2009m01_seq start with 1 increment by 1 minvalue 1 cache 1;
CREATE TABLE measurement_y2009m01 (
    id int nextval('measurement_y2009m01_seq'::regclass),
    CHECK ( logdate >= DATE '2009-01-01' AND logdate < DATE '2009-02-01' )
) INHERITS (measurement);
-- Trigger function to split out between the various child partition tables
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
DECLARE
    r measurement%rowtype;
BEGIN
    IF ( NEW.logdate >= DATE '2007-02-01' AND NEW.logdate < DATE '2007-03-01' ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*) RETURNING * INTO r;
    ELSIF ( NEW.logdate >= DATE '2007-03-01' AND NEW.logdate < DATE '2007-04-01' ) THEN
        INSERT INTO measurement_y2007m03 VALUES (NEW.*) RETURNING * INTO r;
    ... 
    ELSIF ( NEW.logdate >= DATE '2009-01-01' AND NEW.logdate < DATE '2009-02-01' ) THEN
        INSERT INTO measurement_y2009m01 VALUES (NEW.*) RETURNING * INTO r;
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN r;
END;
$$
LANGUAGE plpgsql;

-- Trigger to invoke the insert trigger
CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

-- Trigger function to delete from the master table after the insert
CREATE OR REPLACE FUNCTION measurement_delete_master() RETURNS trigger
    AS $$
DECLARE
    r measurement%rowtype;
BEGIN
    DELETE FROM ONLY measurement where id = new.id returning * into r;
    RETURN r;
end;
$$
LANGUAGE plpgsql;

-- Create the after insert trigger
create trigger after_insert_measurement_trigger
    after insert on measurement
    for each row
        execute procedure measurement_delete_master();

Thanks for the workaround - just what we needed.

if you say RETURN r in trigger function, this will cause to insert same row both partitioned table and master table.

Have better solution?

@travliju yes that is right. This solution won't work :(

@travijuu, @coderdan, there is measurement_delete_master trigger, which sort of handles this situation - deletes row from master table.

We need a better solution to this problem. Another alternative suggested is partitioning using rules but there are some disadvantages that make the solution suggested by @copiousfreetime preferable in the cases I've encountered.

@travijuu, @coderdan - line 82 RETURN r seems like you'll end up with the same problem but I tested and it works as proposed - probably because it's an after insert trigger not a before insert trigger...

@ReinsBrain: RhodiumToad in #postgresql on FreeNode came up with this method that uses views instead: https://gist.github.com/RhodiumToad/b82aac9aa4e3fbdda967d89b1e418aa4 . In this case, the view would become the target of all activity (select/insert/update/delete).