On Mon, Oct 12, 2009 at 03:36:47PM -0500, Dave Huber wrote: > > Surely, there are valid cases of having a function create a > > function. Suppose (just off the top of my head), you create a > > helper function that generates triggers on a table for record > > archiving. > > My application is for archiving. I'm using partitioned tables (each > 100000 records) That seems awfully small. Are the rows really wide? > to keep a rolling archive of the last 10 million. I already need to > setup a trigger function for inserting into the latest partition. > What I'd like to do is automate the process of creating a new table > with a name based off the record ID, a bigserial that is used to > defining each partition. Whether its automated or not, every 100000 > record inserts will require 3 actions: > > 1. create a new partition, This part is just begging for nasty race conditions. It's better to pre-create your partitions. > 2. modify the insert trigger function, and That, you could do with CREATE OR REPLACE. > 3. truncate and drop the oldest partition. If you're going to drop it, you don't need to truncate, and /vice versa/. > A number of things complicate the process for me. I'm using a COPY > statement to bulk insert a varying number of records that can occur > at different intervals of time. Also, the application starts and > stops at various times, so it must be able to initialize based on > the state of the database on startup. > > Is there a way to dynamically create table names within a function > based on a numeric value? I want to be able to name the partitions > log_1, log_2, ..., log_x, where x is the record ID / 100000. For the reason I stated earlier, on-demand DDL is not a great idea. You can write a program in whatever language is convenient to generate the CREATE TABLE statements and feed that in. > I would also have to be able to truncate the same way, by > dynamically specifying the name of the table to be deleted. You could make an SQL function that executes a TRUNCATE. > Well, I appreciate the help, anyway. More help is available :) Cheers, David. P.S. Thanks for giving more context to what you're doing. It's always handy to have some of that :) -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
