I took a look at a few of the most recent bulk edit cases for pg_proc.h: There were two this year: * The addition of proparallel  * The addition of protransform 
And prior to that the most recent seems to be from 2012: * The addition of proleakproof  Quick TLDR - the changes needed to reflect these are super simple to reflect when generating SQL for CREATE FUNCTION statements. Attached is the SQL that would generate function definitions prior to proleakproof and the diffs that would be required after adding support for proleakproof, protransform and proparallel. Each of the diffs indicates the changes that would be needed after the new column is added, the question of how to populate default values for the new columns is beyond the scope that can easily be expressed in general terms and depends entirely on what the nature of the new column is. Note: Currently I have focused on the 'pure' functions, e.g. not the drivers of type serialization, language validation, operators, or other object types. I would want to deal with each of those while handling the conversion for each of those object types in turn. Additional modifications would likely be needed for other types of functions.  https://github.com/postgres/postgres/commit/7aea8e4f2daa4b39ca9d1309a0c4aadb0f7ed81b  https://github.com/postgres/postgres/commit/8f9fe6edce358f7904e0db119416b4d1080a83aa  https://github.com/postgres/postgres/commit/cd30728fb2ed7c367d545fc14ab850b5fa2a4850 On Fri, Dec 11, 2015 at 12:55 PM, Caleb Welton <cwel...@pivotal.io> wrote: > Makes sense. > > During my own prototyping what I did was generate the sql statements via > sql querying the existing catalog. Way easier than hand writing 1000+ > function definitions and not difficult to modify for future changes. As > affirmed that it was very easy to adapt my existing sql to account for some > of the newer features in master. > > The biggest challenge was establishing a sort order that ensures both a > unique ordering and that the dependencies needed for SQL functions have > been processed before trying to define them. Which effects about 4/1000 > functions based on a natural oid ordering. > > > On Dec 11, 2015, at 11:43 AM, Alvaro Herrera <alvhe...@2ndquadrant.com> > wrote: > > > > Caleb Welton wrote: > >> I'm happy working these ideas forward if there is interest. > >> > >> Basic design proposal is: > >> - keep a minimal amount of bootstrap to avoid intrusive changes to core > >> components > >> - Add capabilities of creating objects with specific OIDs via DDL > during > >> initdb > >> - Update the caching/resolution mechanism for builtin functions to be > >> more dynamic. > >> - Move as much of bootstrap as possible into SQL files and create > catalog > >> via DDL > > > > I think the point we got stuck last time at was deciding on a good > > format for the data coming from the DATA lines. One of the objections > > raised for formats such as JSON is that it's trivial for "git merge" (or > > similar tools) to make a mistake because object-end/object-start lines > > are all identical. And as for the SQL-format version, the objection was > > that it's hard to modify the lines en-masse when modifying the catalog > > definition (new column, etc). Ideally we would like a format that can > > be bulk-edited without too much trouble. > > > > A SQL file would presumably not have the merge issue, but mass-editing > > would be a pain. > > > > Crazy idea: we could just have a CSV file which can be loaded into a > > table for mass changes using regular DDL commands, then dumped back from > > there into the file. We already know how to do these things, using > > \copy etc. Since CSV uses one line per entry, there would be no merge > > problems either (or rather: all merge problems would become conflicts, > > which is what we want.) > > > > -- > > Álvaro Herrera http://www.2ndQuadrant.com/ > > PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services >
*** gen_protransform.sql 2015-12-11 14:36:25.000000000 -0800 --- gen_proparallel.sql 2015-12-11 14:35:41.000000000 -0800 *************** *** 14,19 **** --- 14,23 ---- ELSE '' END || CASE WHEN proisstrict THEN ' STRICT' ELSE '' END || CASE WHEN proleakproof THEN ' LEAKPROOF' ELSE '' END + || CASE proparallel WHEN 's' THEN ' PARALLEL SAFE' + WHEN 'r' THEN ' PARALLEL RESTRICTED' + WHEN 'u' THEN '' -- PARALLEL UNSAFE is DEFAULT + ELSE '' END || CASE WHEN (procost != 1 and lanname = 'internal') OR (procost != 100 and lanname = 'sql') THEN ' COST '
Description: Binary data
*** gen_leakproof.sql 2015-12-11 14:36:09.000000000 -0800 --- gen_protransform.sql 2015-12-11 14:36:25.000000000 -0800 *************** *** 72,77 **** AND prorettype != 'anyenum'::regtype /* Enum is special */ AND 'anyenum'::regtype not in (SELECT unnest(proargtypes)) AND not proiswindow /* Window Functions */ order by p.prolang, p.oid ; - --- 72,78 ---- AND prorettype != 'anyenum'::regtype /* Enum is special */ AND 'anyenum'::regtype not in (SELECT unnest(proargtypes)) AND not proiswindow /* Window Functions */ + AND protransform = 0 /* Transforms */ + AND p.oid not in (SELECT protransform from pg_proc) order by p.prolang, p.oid ;
*** gen_start.sql 2015-12-11 14:35:53.000000000 -0800 --- gen_leakproof.sql 2015-12-11 14:36:09.000000000 -0800 *************** *** 13,18 **** --- 13,19 ---- WHEN 'v' THEN '' -- VOLATILE is DEFAULT ELSE '' END || CASE WHEN proisstrict THEN ' STRICT' ELSE '' END + || CASE WHEN proleakproof THEN ' LEAKPROOF' ELSE '' END || CASE WHEN (procost != 1 and lanname = 'internal') OR (procost != 100 and lanname = 'sql') THEN ' COST '
-- Sent via pgsql-hackers mailing list (email@example.com) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers