From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Pgsql-performance <pgsql-performance@postgresql.org>
Subject: Partition with check constraint with "like"
Date: Fri, 21 May 2021 00:32:11 +0000 (UTC)
Message-ID: <87840670.92362.1621557131080@mail.yahoo.com> (raw)
References: <87840670.92362.1621557131080.ref@mail.yahoo.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: nagaraj.sf@yahoo.com
Subject: Re: Partition with check constraint with "like"
In-Reply-To: <87840670.92362.1621557131080@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox