Auto-partitioning in PostgreSQL 10 - Mailing list pgsql-hackers

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.

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;