PostgreSQL error: query string argument of EXECUTE is null

The error message is

query string argument of EXECUTE is null

You have two EXECUTE commands:

_query := 'CREATE TABLE public.'        || quote_ident(_table_name) || ' ( ) INHERITS (public.evidence)';EXECUTE _query;...EXECUTE 'INSERT INTO public.'      || quote_ident(_table_name) || ' VALUES ($1.*)' USING NEW;

The only part that can be NULL is table_name.
The only chance for table_name to become NULL is here:

SELECT raised_local_time FROM notifications WHERE id=_notification_idINTO _raised_local_time;

So the cause must be one of two reasons:

1. NEW.notification_id is NULL.

2. There is no row in notifications for the given NEW.notification_id.

Try this modified trigger function for debugging:

CREATE OR REPLACE FUNCTION partition_evidence_by_month()  RETURNS trigger AS$func$DECLARE    _table_name text;BEGIN   SELECT 'evidence-' || to_char(raised_local_time, 'YYYY-MM')   FROM   public.notifications -- schema-qualify to be sure   WHERE  id = NEW.notification_id   INTO   _table_name;   IF _table_name IS NULL THEN      RAISE EXCEPTION '_table_name is NULL. Should not occur!';   END IF;   IF NOT EXISTS (   -- create table if it does not exist      SELECT 1      FROM   pg_catalog.pg_class c      JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace      WHERE  c.relkind = 'r'      AND    c.relname = _table_name      AND    n.nspname = 'public') THEN      EXECUTE 'CREATE TABLE public.'            || quote_ident(_table_name) || ' ( ) INHERITS (public.evidence)';   END IF;   EXECUTE 'INSERT INTO public.'         || quote_ident(_table_name) || ' VALUES $1'  -- Use NEW row directly   USING  NEW;       -- write data to the partition table   RETURN NULL;END$func$ LANGUAGE plpgsql;

• Remove unused variables and simplify code. (This is obviously a simplified example.)

  • Among other things, you don't need date_trunc() at all. Simply feed the original timestamp to to_char().

  • No point in using varchar(n). Simply use text or varchar.

  • Avoid too many assignments where unnecessary - comparatively expensive in PL/pgSQL.

• Add a RAISE to check my hypothesis.
  If you get the error message, discriminating between the two possible causes would be the next step. Should be trivial ...