postgresql - function/procedure to update (ALTER) another function/procedure -

i have partitioned tables on postgresql 9.2 each , every 1 partition function looks this:

create or replace function mypartitionselectionfunction()   returns trigger $body$ begin     if ( new.partitioncolumn < date '2010-08-08 00:00:00') insert mypartitionedtable_week_31_2010 values (new.*);     elsif ( new.partitioncolumn < date '2010-08-15 00:00:00' , new.partitioncolumn >=  date '2010-08-08 00:00:00') insert mypartitionedtable_week_32_2010 values (new.*);     else         raise exception 'partitioncolumn out of range. update mypartitionselectionfunction()';     end if;     return null; end; $body$ language plpgsql 

so, each week new partition. problem updating hundreds of functions every single week. have automate that, , trigger each insert isn't feasible. idea create function called cron updates each partition function, adding elsif before final else. can't find way iterate through each function has "partition" name on it, retrieve it's code (psql commands \d won't work in function, right?) add new elsif , update (alter) each function accordingly, cretaing new partition in process.

so, biggest question have is:

how iterate (using cursor?) through each function once has "partition" name on it, retrieve it's code, add elsif before else , update (alter) it's contents not risking getting stuck in infinite loop?

any appreciated. thanks.

while can retrieve function source pg_proc, trying retrieve it, edit , update function recipe pain , suffering.

instead, write pl/pgsql procedure generates function text in entirety. use query against information_schema or pg_catalog.pg_class required table list, constraints, inheritance, etc. loop on query results, generating body of function, join single block of text , pass execute.

here's toy example of 1 function generating another:

create or replace function very_meta(func_name text, message text) returns void  $$ declare     func_lines text; begin     -- in reality you'd build iteratively, or preferably use `string_agg` on     -- query, i'm going supply single line function body     -- example:     func_lines := format($line$raise notice 'it works, message %%!','%s';$line$, message);     -- now, build function creation statement , execute it:     execute format(     $inner$         -- sql text we're going execute, %%i placeholder         -- replaced format(...) function:         --         create or replace function %i() returns void         $innerbody$         begin             -- , body of function we're generating             -- in case it's going substituted in func_lines             -- format(...)             %s         end;         $innerbody$         language plpgsql;     $inner$, func_name, func_lines); end; $$ language plpgsql; 

demo:

regress=> select very_meta('lessmeta', 'secret message');  very_meta  -----------  (1 row)  regress=> select lessmeta(); notice:  works, message secret message!  lessmeta  ----------  (1 row) 

this can combined appropriate queries against information_schema and/or system catalogs table lists, create series of conditional tests, etc. this answer wrote while ago details how query catalogs inheritance relationships, might useful, you'll need identify check constraint in use. if have naming convention might simpler rely on , search of pg_class.

do keep in mind postgresql's partitioning works best @ few hundred tables, if that. performance fall off due query planning cost larger table counts.