Skip to content

Instantly share code, notes, and snippets.

@Lurunchik
Last active August 29, 2015 14:00
Show Gist options
  • Save Lurunchik/11256656 to your computer and use it in GitHub Desktop.
Save Lurunchik/11256656 to your computer and use it in GitHub Desktop.
PostgreSQL: Partition with returning <column> solved "0 rows problem"
--partitioning like http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html
--create master table
CREATE TABLE master_table
(
id serial,
key character varying NOT NULL,
value character varying NOT NULL,
CONSTRAINT master_table_pkey PRIMARY KEY (id)
);
--create slave tables with constraint
CREATE TABLE slave_table_1
(
CONSTRAINT slave_table_check CHECK (id < 1000)
)
INHERITS (master_table);
CREATE TABLE slave_table_2
(
CONSTRAINT slave_table_check CHECK (id >= 1000)
)
INHERITS (master_table);
--return null instead of new calls "0 row" problem
CREATE OR REPLACE FUNCTION master_insert_trigger()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.id < 1000 THEN
INSERT INTO slave_table_1 SELECT NEW.*;
ELSE
INSERT INTO slave_tablee_2 SELECT NEW.*;
END IF;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
--In this case we have a "0 rows" problem when we do insert in master table
--Solution: we create updatable view of master_table and then we will insert to master_view instead of master_table
CREATE VIEW master_view AS SELECT * from master_table;
--to prevent a null values instead of default, we should create default valuews on master_view like on master_table
ALTER VIEW master_view ALTER COLUMN id SET DEFAULT nextval('master_table_id_seq'::regclass);
--create insert trigger on view with return new solved "0 rows" problem without insert to master table
CREATE OR REPLACE FUNCTION public.insert_trigger()
RETURNS trigger AS
$BODY$
BEGIN
RAISE NOTICE 'master';
INSERT INTO master_table SELECT NEW.*;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
CREATE TRIGGER insert_trigger
INSTEAD OF INSERT
ON public.master_view
FOR EACH ROW
EXECUTE PROCEDURE public.view_insert_trigger();
-- to emulate after/before triggers on master_view we should create INSTEAD OF INSERT triggers in alphabetic order
--For example: before_insert and post_insert (b>i, p<i)
CREATE TRIGGER before_insert_trigger
INSTEAD OF INSERT
ON public.master_view
FOR EACH ROW
EXECUTE PROCEDURE public.before_trigger_function();
CREATE TRIGGER post_insert_trigger
INSTEAD OF INSERT
ON public.master_view
FOR EACH ROW
EXECUTE PROCEDURE public.after_trigger_function();
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment