PostgreSQL SQL function definition tables Last night I was creating a code generator using Code Smith that would create C# functions for every database function in a schema but I needed a SQL statement that would get me the definition of the function. It wasn't obvious how to retrieve the parameters and the return type for the already existing functions that were in the database. At first I thought I would be able to find a column in the database that had the DDL and parse it out for the values I needed but no such column existed.

So here is what I discovered. When a database function is being added, PostgreSQL parses out the database function parameters, return type, namespace, name of the function and body and inserts them into some tables and columns. When the parse of the parameters is done, the parameter names are placed in one column surrounded on the ends by brackets and the parameter types are placed in another column. When the parameter types are stored, they are stored as their numerical representations instead of the names like "integer" and "numeric". Below is a list of some of the fairly commonly used types and their numbers.

• 1043 char varying
• 21 smallint
• 1114 timestamp
• 23 integer
• 1700 numeric
• 20 bigint
• 25 text
• 1082 date
• 16 bool
• 869 inet
• 2279 trigger
• 1790 set of refcursor

If you want to look at a list of all of the functions for a schema, the following SQL query will return the data needed. This SQL query was mainly derived from looking at EMS.

SELECT p.proname AS name, p.oid, p.proargtypes AS args, ds.description , p.prorettype AS rettype,
p.proretset, p.probin, p.proisstrict AS strict, p.prosrc AS body, l.lanname AS lang,
u.usename, p.prosecdef, p.provolatile, p.proisagg, n.nspname, proargnames, p.proargmodes, p.proallargtypes
FROM pg_proc p
LEFT OUTER JOIN pg_description ds ON ds.objoid = p.oid
INNER JOIN pg_namespace n ON p.pronamespace = n.oid
INNER JOIN pg_language l ON l.oid = p.prolang
LEFT OUTER JOIN pg_user u ON u.usesysid = p.proowner
WHERE n.nspname = 'public'
ORDER BY p.proname, n.nspname

"nspname" is the schema name that the functions will be pulled from. The parameters list that has the integer type definitions will be in the "args" column and the parameter names will be in the "proargnames" column. The "body" column contains the body of the function.

I will try to post the Code Smith template that I created for generating the C# classes soon.  ¶ 5:39 PM