Why does NEW.name return NULL? PostgreSQL 11

Question

I wrote a function that should automatically create new partitions for the table. I created a trigger, but when the trigger fires and the function is called, nothing happens, just an error appears:

ERROR: query string argument of EXECUTE is null

Function code:

CREATE FUNCTION public.auto_part()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF 
AS $BODY$DECLARE
    partition_date TEXT;
    partition TEXT;
    startdate TEXT;
    enddate TEXT;
    query TEXT;
BEGIN
    partition_date := TO_CHAR(new.created_on,'YYYY-MM');
    startdate := partition_date || '-01';
    enddate := to_char(to_timestamp('YYYY-MM',partition_date) + '1 MONTH'::interval,'YYYY-MM') || '-01';
    
    partition := TG_TABLE_NAME || '_' || partition_date;
    
    IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
        RAISE NOTICE 'TABLE %',TG_TABLE_NAME;
        RAISE NOTICE 'ID %',new.id;
        RAISE NOTICE 'NAME %',new.username;
        RAISE NOTICE 'CREATED_ON %',new.created_on;
        RAISE NOTICE 'PARTITION_DATE %',partition_date;
        RAISE NOTICE 'STARTDATE %',startdate;
        RAISE NOTICE 'A partition has been created %',partition;
        EXECUTE 'CREATE TABLE ' || partition || ' PARTITION OF ' || TG_TABLE_NAME || ' FOR VALUES FROM ('|| startdate || ') TO (' || enddate || ');';
        --RAISE NOTICE query;
        --EXECUTE query;
        RETURN 1;
    END IF;
END
$BODY$;

Conclusion "RAISE NOTICE":

NOTICE:  TABLE users_sec
NOTICE:  ID <NULL>
NOTICE:  NAME <NULL>
NOTICE:  CREATED_ON <NULL>
NOTICE:  PARTITION_DATE <NULL>
NOTICE:  STARTDATE <NULL>
NOTICE:  A partition has been created <NULL>

ERROR:  query string argument of EXECUTE is null
CONTEXT:  PL/pgSQL function auto_part() line 22 at EXECUTE
SQL state: 22004

Trigger code:

CREATE TRIGGER auto_part_trigger
    BEFORE INSERT OR UPDATE 
    ON public.users_sec
    FOR EACH STATEMENT
    EXECUTE PROCEDURE public.auto_part();

Insert example:

INSERT INTO users_sec(
    username, password, created_on, last_logged_on)
    VALUES (
        'qwerty',
        random_string( 20 ),
        '2021-03-23',
        '2021-03-24'
    );

Table creation code:

CREATE TABLE public.users_sec
(
    id integer NOT NULL DEFAULT nextval('users_sec_id_seq'::regclass) ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    username text COLLATE pg_catalog."default" NOT NULL,
    password text COLLATE pg_catalog."default",
    created_on timestamp with time zone NOT NULL,
    last_logged_on timestamp with time zone NOT NULL,
    CONSTRAINT users_sec_pkey PRIMARY KEY (id, created_on)
) PARTITION BY RANGE (created_on) 
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

Answer 1

If you want NEW to contain the row about to be inserted, you have to use a FOR EACH ROW level trigger.

Since you cannot have a BEFORE trigger FOR EACH ROW on a partitioned table, that's kind of a catch 22 situiation.

A possible way our may be this:

• Create a DEFAULT partition. All rows that don't match an existing partition will be inserted there.

• Define a BEFORE trigger FOR EACH ROW on the default partition. The trigger creates a new partition as appropriate and inserts the row into that partition. The trigger function uses RETURN NULL to avoid inserting anything in the default partition itself.

This way, the default partition remains empty. Moreover, you only have to pay the overhead for a trigger for rows that don't go in any of the existing partitions!