There is a geo table, 100 geo_1 , geo_2 , ..., geo_100 tables are inherited from it. I need when prompted

INSERT INTO geo VALUES(50, 100)

redirect to geo_50,

  INSERT INTO geo VALUES(25, 100)

redirect to geo_25,

I wrote this function:

 CREATE OR REPLACE FUNCTION public.geo_ins() RETURNS trigger AS $BODY$ DECLARE partition_name varchar := 'geo_' || new.object_id; begin EXECUTE 'INSERT INTO geo_' || new.object_id || ' SELECT new.*'; return null; end; $BODY$ LANGUAGE plpgsql;

She jerks like this trigger:

 CREATE TRIGGER geo_partition_ins BEFORE INSERT ON public.geo FOR EACH ROW EXECUTE PROCEDURE public.geo_ins();

But this function does not work, when trying to embed it writes:

 ОШИБКА: таблица "new" отсутствует в предложении FROM LINE 1: INSERT INTO geo_100 SELECT new.* ^ QUERY: INSERT INTO geo_100 SELECT new.* CONTEXT: функция PL/pgSQL geo_ins(), строка 5, оператор EXECUTE ********** Error ********** ОШИБКА: таблица "new" отсутствует в предложении FROM SQL state: 42P01 Context: функция PL/pgSQL geo_ins(), строка 5, оператор EXECUTE

What do I need to fix?

  • Well, yes, inside execute new is not available, it has its own scope. What to fix is ​​to change the database structure by replacing 100 tables with one - Mike
  • This section, I specifically broke the table into several, because it will be huge. Is there any other option? - IndexOutOfRange
  • To understand the work - habrahabr.ru/post/273933 . But do not do the sunset manually, use the pg_partman extension. - Shallow
  • Thanks, found the answer in this article. - IndexOutOfRange
  • In the end, everything is done like this: CREATE OR REPLACE FUNCTION public.geo_ins () RETURNS trigger AS $ BODY $ DECLARE v_partition_name text; BEGIN v_partition_name: = format ('geo_% s', NEW.object_id); execute 'INSERT INTO' || v_partition_name || 'VALUES (($ 1). *)' USING NEW; return NULL; END; $ BODY $ LANGUAGE plpgsql; - IndexOutOfRange

0