Hi,
I am trying to create partitions on the table based on first letter of the column record value using inherit relation & check constraint.
Somehow able to create and load the data into the tables as per my requirement.
But the problem is when querying the data on that partitioned column, it's referring to all the children's tables instead of the matching table.
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:
Any suggestions.
Thanks,
Rj
