Partition with check constraint with "like"

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 LEAKPROOFAS $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<>fiddle

|
|
| |
Postgres 11 | db<>fiddle

Free online SQL environment for experimenting and sharing.
|

|

|

Any suggestions.

Thanks,Rj