Thread: creating a function with another function
Is it possible to execute a CREATE OR REPLACE FUNCTION with another function or even have a function modify itself?
Dave
This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message.
THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity.
WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
On Mon, Oct 12, 2009 at 02:12:35PM -0500, Dave Huber wrote: > Is it possible to execute a CREATE OR REPLACE FUNCTION with another > function or even have a function modify itself? Yes, but doing any of that is a sign that you're working with a broken design. That, or you're looking to make an entry in the International Obfuscated Database Programming Contest ;) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Mon, Oct 12, 2009 at 3:41 PM, David Fetter <david@fetter.org> wrote: > On Mon, Oct 12, 2009 at 02:12:35PM -0500, Dave Huber wrote: >> Is it possible to execute a CREATE OR REPLACE FUNCTION with another >> function or even have a function modify itself? > > Yes, but doing any of that is a sign that you're working with a broken > design. That, or you're looking to make an entry in the International > Obfuscated Database Programming Contest ;) 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. I agree though that having a function modify itself sounds like something that is much better done as dynamic sql (inside a function). merlin
> 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) to keep a rolling archive of the last10 million. I already need to setup a trigger function for inserting into the latest partition. What I'd like to do isautomate the process of creating a new table with a name based off the record ID, a bigserial that is used to definingeach partition. Whether its automated or not, every 100000 record inserts will require 3 actions: 1. create a new partition, 2. modify the insert trigger function, and 3. truncate and drop the oldest partition. A number of things complicate the process for me. I'm using a COPY statement to bulk insert a varying number of records thatcan occur at different intervals of time. Also, the application starts and stops at various times, so it must be ableto 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 thepartitions log_1, log_2, ..., log_x, where x is the record ID / 100000. I would also have to be able to truncate the sameway, by dynamically specifying the name of the table to be deleted. Well, I appreciate the help, anyway. Thanks, Dave This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message,together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use,printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message inerror, please immediately advise the sender by reply email message to the sender and delete all copies of this message. THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similarimport, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachmentsare an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies,Inc., or any of its subsidiaries, affiliates, or any other person or entity. WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the companycannot accept responsibility for any loss or damage arising from the use of this email or attachments.
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@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate