Re: 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
int_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)' 
>                                                                                                                 || '
FORVALUES IN ( '''|| NEW.id_bac ||''' ) ;'; 
>
>
>
>
>                                                    EXECUTE new_table;
>                                                    ELSE
>                new_table:= "The table exist already";                                         --Table already exists,
donot create table 
>                END IF;
>
>
>                                                    new_insert := 'INSERT INTO
t_dossier_bac_history_sensor_collectionsVALUES(''' 
>                                                                                                                 ||
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||');'; 
>
>
--RAISENOTICE '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