I like the plpgsql procedural language for PostgreSQL more than it is appropriate to like a procedural language. I find the reward:effort ratio to be quite high. Since one can do so many cool things with it, why waste time writing boring insert functions for tables?
Here’s my plpgsql function which generates the create function statements for tables in a database.
The above code is a rolled back transaction which creates the generator function and then calls it so that one can view the results. By itself, it leaves no lasting impact on the database.
The insert generator accepts no input and returns a varchar. The string it returns can be executed against the database to create one function for each table in the database.
The newly created functions will accept as input all of the fields of the table and returns the primary key of the new record or 0 (zero) if no record was inserted.
The insert generator is limited to working with somewhat simple tables, as it is not aware of multiple column primary keys.
The resulting functions assume that they are being supplied clean input. You should do something to scrub your data before handing it off to the insert functions generated using this method. There’s a lot else that this doesn’t do, so you should use it at your own risk. I make no guarantees that it will work in your environment or that it is suitable for any particular use.
I will post a function which generates update functions in the coming days.
I have tested this code on against PostgreSQL versions 8.1.9 and 7.4.18.
