src/include/catalog/README.add_catalog_function

Steps to add a function to the catalogs
========================================

The steps listed below should be followed if a new function needs to be added 
to the catalog. For instance, if we want to add a new function, fn_sql(),
that will expose the backend function, fn_be() as a built_in function,
the following steps are needed. Note, the same set of steps need to be 
followed if adding a new type.

1. Get an unused OID by running the script unused_oids located in 
   src/include/catalog/unused_oids. Claim an unused OID. For eg, for our
   example we are claiming 5075.

2. Add the new function definition in src/include/catalog/pg_proc.sql.
   An example function definition: 
   	CREATE FUNCTION fn_sql(int4, int4) RETURNS bool LANGUAGE 
  		internal VOLATILE NO SQL AS 'fn_be' 
		WITH (OID=5075, DESCRIPTION="Description of the function");
   If adding a new type, the type definition needs to be added to pg_type.sql.

3. In src/include/catalog make pg_type.h and pg_proc.h writable 
   by opening them 
	p4 edit pg_type.h
	p4 edit pg_proc.h 
   Note, the above step needs to be done when adding a type, or a function, or both.

4. Run the following command in src/include/catalog:

   $ perl catullus.pl -procdef pg_proc.sql -prochdr pg_proc.h -typedef pg_type.sql -typehdr pg_type.h

  catullus.pl will re-generate the pg_proc.h file with the appropriate 
  DATA statement for the fn_sql function definition. If a new type 
  was being added in pg_type.sql, pg_type.h would be updated with the 
  appropriate DATA statements. 

  For more information on catullus.pl, please refer to 
  src/include/catalog/README.tidycat.

5. Since the catalogs are being modified, the catalog number in 
   src/include/catalog/catversion.h should be bumped up. This is to 
   indicate an initdb is required so the version number in the backend matches
   the version number on disk.
 
   Since catversion is bumped up, we have to regenerate the catalog json file,
   tools/bin/gppylib/data/X.X.json. Otherwise, catversion test would fail.

   $ perl tidycat.pl -dd foo.json -df json *.h

   After tidycat.pl generates foo.json, rename it with the major release version 
   and move it to tools/bin/gppylib/data/


6. Adding a new function to the catalog has an impact on upgrade. Add a new 
   entry to the appropriate upgrade script. For instance if the new function 
   was being added in 4.2, modify the 
   src/test/regress/data/upgrade42/upg2_catupgrade_42.sql.in
   script and add the following SQL to add the function, fn_sql, under
   the schema, pg_catalog, to it: 

   	CREATE FUNCTION @gpupgradeschemaname@.fn_sql(int4, int4) 
		RETURNS bool LANGUAGE internal IMMUTABLE STRICT 
		AS 'fn_be' WITH (OID=5075);
	COMMENT ON FUNCTION @gpupgradeschemaname@.fn_sql(int4, int4) 
		IS 'Description of the function';

7. Run make distclean, make, gpinitsystem and you should be able to see your 
   newly added function in pg_proc. Newly added types will be visible in
   pg_type.

8. Make sure you run and possibly repair the upgrade regression test in 
   install-chek.

9. Don't forget to checkin the generated files, pg_type.h and pg_proc.h, along
   with any modified files.