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;
