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:
NEW.notification_id
isNULL
.There is no row in
notifications
for the givenNEW.notification_id
.
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 toto_char()
.No point in using
varchar(n)
. Simply usetext
orvarchar
.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 ...