Marc Evans wrote: > 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 You can view (1) as a positive, insofar as you can add/remove rules on a monthly basis to "turn on" and "turn off" inserts into monthly partitions as time goes on (i.e. "freeze" previous partitions). As for (3), you can supply a stored procedure that does the INSERTs, and guarantee cooperation by not giving INSERT permission to the underyling table(s).
