Re: creating a function with another function

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(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate