Hello -
I have a schema setup which I want to use partitions with. The intent is
to partition based on the "created_at" column, seperating based on the
year/month. What I am struggling with is a rule that I can use to
automatically determine which partition data should be inserted into, such
that I want to derive the table name based on the data being inserted. For
example, a non-functional rule that shows my goal is:
create or replace rule test_partition as insert into tests do instead
insert into (select 'tests_' ||
(extract(year from NEW.created_at) * 100 +
extract(month from NEW.created_at))::text)
values (id,created_at,data);
In the above, the sub expression to derive the table name doe not parse.
My question is, what could I do instead of the above to achieve the same?
Options that I am aware of include:
* Use a list of hard coded table name and range check combinations. This
option doesn't scale over time, e.g. you are always needing to expand
the list of table names over time.
* Modify application code to directly insert into the partition. This is
not very friendly to the programmer(s), and is far less flexible over
time, should the partitioning logic need to change.
* Create a function which returns the table name to be used, which the
application code then uses to insert directly into. This to some extent
resolves the above 2 issues, though requires cooperation of the
application programmers, which I'd ideally like to avoid.
Any suggestions?
- Marc