Re: Auto-partitioning in PostgreSQL 10

> On Jun 25, 2018, at 3:00 AM, zafiirah jumeen <zafiirahjumeen(at)gmail(dot)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