Auto-partitioning in PostgreSQL 10

On Jun 25, 2018, at 3:00 AM, zafiirah jumeen <zafiirahjumeen@gmail.com> wrote:

Hello,

I was trying to do auto partitioning in PostgreSQL 10.
First of all, I created 2 tables t_dossier_bac_history_insert_table and t_dossier_bac_history_sensor_collections.
And then, I created a trigger which would execute a function (which would create my partitions) before inputting data in t_dossier_bac_history_insert_table.
But, I am having an error which is as follows when I try to insert data from an existing table to t_dossier_bac_history_insert_table:

ERROR: query string argument of EXECUTE is null CONTEXT: PL/pgSQL function sensor_partition() line 27 at EXECUTE SQL state: 22004

Could you please help me in resolving this issue.

The variable 'new_insert' will be null when any of the new columns
are null, because concatenation of null with anything else renders
a null result. You could look at the documentation for the function
named COALESCE() and see how to use that to prevent the 'new_insert'
variable from becoming null.

There may be broader problems in the general design of your solution,
too.

I think you should post questions of this sort to pgsql-general
rather than here.

Please see below for part of my codes.
Thank you in advance.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE t_dossier_bac_history_insert_table
(
id_dossier_bac_history character(32) COLLATE pg_catalog."default" NOT NULL,
boo_supprime boolean,
dat_create timestamp without time zone DEFAULT timezone('utc'::text, now()),
dat_supprime timestamp without time zone,
dat_update timestamp without time zone,
num_version bigint NOT NULL,
dat_date_entree timestamp without time zone,
dat_date_sortie timestamp without time zone,
id_bac character(32) COLLATE pg_catalog."default" NOT NULL,
id_dossier character(32) COLLATE pg_catalog."default" NOT NULL,
boo_en_migration boolean DEFAULT false

)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

CREATE TABLE t_dossier_bac_history_sensor_collections
(
id_dossier_bac_history character(32) COLLATE pg_catalog."default" NOT NULL,
boo_supprime boolean,
dat_create timestamp without time zone DEFAULT timezone('utc'::text, now()),
dat_supprime timestamp without time zone,
dat_update timestamp without time zone,
num_version bigint NOT NULL,
dat_date_entree timestamp without time zone,
dat_date_sortie timestamp without time zone,
id_bac character(32) COLLATE pg_catalog."default" NOT NULL,
id_dossier character(32) COLLATE pg_catalog."default" NOT NULL,
boo_en_migration boolean DEFAULT false

)
PARTITION BY LIST (id_bac)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

CREATE TRIGGER insert_to_t_dossier_bac_history_sensor_collections
BEFORE INSERT
ON t_dossier_bac_history_insert_table
FOR EACH ROW
EXECUTE PROCEDURE sensor_partition();

CREATE OR REPLACE FUNCTION sensor_partition()
RETURNS TRIGGER AS $$
DECLARE
sensor_table TEXT;
new_table TEXT;
new_insert TEXT;
BEGIN
sensor_table='id_bac_'||NEW.id_bac;

IF NOT EXISTS (SELECT relname FROM pg_class --CHECK IF TABLE 'sensor_table' exists. If not, create the table.
WHERE relname=sensor_table) THEN
RAISE NOTICE 'Creating Partition:%', sensor_table;

new_table := 'CREATE TABLE '|| sensor_table --Table does not exists, create table/partition
|| ' PARTITION OF t_dossier_bac_history_sensor_collections' || ' (id_dossier_bac_history, dat_create, dat_supprime, dat_update, num_version, dat_date_entree, dat_date_sortie, id_bac, id_dossier, boo_en_migration)'
|| ' FOR VALUES IN ( '''|| NEW.id_bac ||''' ) ;';

EXECUTE new_table;
ELSE
new_table:= "The table exist already"; --Table already exists, do not create table
END IF;

new_insert := 'INSERT INTO t_dossier_bac_history_sensor_collections VALUES('''
|| NEW.id_dossier_bac_history ||''', '
|| NEW.boo_supprime ||', '''
|| NEW.dat_create ||''','' '
|| NEW.dat_supprime ||''','' '
|| NEW.dat_update ||''', '
|| NEW.num_version ||','' '
|| NEW.dat_date_entree ||''','' '
|| NEW.dat_date_sortie ||''','' '
|| NEW.id_bac ||''','' '
|| NEW.id_dossier ||''', '
|| NEW.boo_en_migration ||');';

--RAISE NOTICE 'Creating Partition:%', NEW.id_dossier_bac_history;
EXECUTE new_insert;
-- RETURN NULL;
END;
$$ LANGUAGE plpgsql VOLATILE;

INSERT INTO t_dossier_bac_history_insert_table
SELECT * FROM t_dossier_bac_history;

mark