> On Jun 25, 2018, at 3:00 AM, zafiirah jumeen <zafiirahjum...@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