create table t1(id int,name text);
CREATE TABLE partition_tab.t1_name_null
(
CONSTRAINT null_check CHECK (name IS NULL)
) INHERITS (t1);
CREATE or replace FUNCTION partition_tab.func_t1_insert_trigger()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
chk_cond text;
c_table TEXT;
c_table1 text;
new_name text;
m_table1 text;
BEGIN
if ( NEW.name is null) THEN
INSERT into partition_tab.t1_name_null VALUES (NEW.*);
elseif ( NEW.name is not null) THEN
new_name:= substr(NEW.name,1,1);
raise info 'new_name %',new_name;
c_table := TG_TABLE_NAME || '_' || new_name;
c_table1 := 'partition_tab.' || c_table;
m_table1 := ''||TG_TABLE_NAME;
IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=lower(c_table)) THEN
RAISE NOTICE 'values out of range partition, creating partition table: partition_tab.%',c_table;
chk_cond := new_name||'%';
raise info 'chk_cond %',chk_cond;
EXECUTE 'CREATE TABLE partition_tab.' || c_table || '(check ( name like '''|| chk_cond||''')) INHERITS (' ||TG_TABLE_NAME|| ');';
END IF;
EXECUTE 'INSERT INTO ' || c_table1 || ' SELECT(' || m_table1 || ' ' || quote_literal(NEW) || ').* RETURNING id;';
END IF;
RETURN NULL;
END;
$BODY$;
CREATE TRIGGER t1_trigger
BEFORE INSERT OR UPDATE
ON t1
FOR EACH ROW
EXECUTE PROCEDURE partition_tab.func_t1_insert_trigger()
examples:
| Postgres 11 | db<>fiddleFree online SQL environment for experimenting and sharing. |
|
|
Any suggestions.
Thanks,
Rj