Here is an implementation of the information_schema.parameters.parameter_default column.
I ended up writing a C function to decode the whole thing from the system catalogs, because it was too complicated in SQL, so I abandoned the approach discussed in [0]. [0]: http://archives.postgresql.org/message-id/1356092400.25658.6.ca...@vanquo.pezone.net
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index ddbc56c..4fa4ab8 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -3323,6 +3323,15 @@ <title><literal>parameters</literal> Columns</title> in future versions.) </entry> </row> + + <row> + <entry><literal>parameter_default</literal></entry> + <entry><type>character_data</type></entry> + <entry> + The default expression of the parameter, or null if none or if the + function is not owned by a currently enabled role. + </entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 2307586..82d686a 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1132,10 +1132,15 @@ CREATE VIEW parameters AS CAST(null AS sql_identifier) AS scope_schema, CAST(null AS sql_identifier) AS scope_name, CAST(null AS cardinal_number) AS maximum_cardinality, - CAST((ss.x).n AS sql_identifier) AS dtd_identifier + CAST((ss.x).n AS sql_identifier) AS dtd_identifier, + CAST( + CASE WHEN pg_has_role(proowner, 'USAGE') + THEN pg_get_function_arg_default(p_oid, (ss.x).n) + ELSE NULL END + AS character_data) AS parameter_default FROM pg_type t, pg_namespace nt, - (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, + (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner, p.proargnames, p.proargmodes, _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x FROM pg_namespace n, pg_proc p diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 266cec5..b9ebb78 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2248,6 +2248,76 @@ static char *generate_function_name(Oid funcid, int nargs, List *argnames, return argsprinted; } +Datum +pg_get_function_arg_default(PG_FUNCTION_ARGS) +{ + Oid funcid = PG_GETARG_OID(0); + int32 argn = PG_GETARG_INT32(1); + HeapTuple proctup; + Form_pg_proc proc; + int numargs; + Oid *argtypes; + char **argnames; + char *argmodes; + int i; + List *argdefaults; + Node *node; + char *str; + int inputargn; + Datum proargdefaults; + bool isnull; + int nth; + + proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid)); + if (!HeapTupleIsValid(proctup)) + elog(ERROR, "cache lookup failed for function %u", funcid); + + numargs = get_func_arg_info(proctup, &argtypes, &argnames, &argmodes); + if (argn > numargs) + { + ReleaseSysCache(proctup); + PG_RETURN_NULL(); + } + + inputargn = 0; + + for (i = 0; i < argn; i++) + { + if (!argmodes || argmodes[i] == PROARGMODE_IN || argmodes[i] == PROARGMODE_INOUT || argmodes[i] == PROARGMODE_VARIADIC) + inputargn++; + } + + proargdefaults = SysCacheGetAttr(PROCOID, proctup, + Anum_pg_proc_proargdefaults, + &isnull); + + if (isnull) + { + ReleaseSysCache(proctup); + PG_RETURN_NULL(); + } + + str = TextDatumGetCString(proargdefaults); + argdefaults = (List *) stringToNode(str); + Assert(IsA(argdefaults, List)); + pfree(str); + + proc = (Form_pg_proc) GETSTRUCT(proctup); + + nth = inputargn - 1 - (proc->pronargs - proc->pronargdefaults); + if (nth < 0 || nth >= list_length(argdefaults)) + { + ReleaseSysCache(proctup); + PG_RETURN_NULL(); + } + node = list_nth(argdefaults, nth); + str = deparse_expression_pretty(node, NIL, false, false, 0, 0); + + ReleaseSysCache(proctup); + + PG_RETURN_TEXT_P(string_to_text(str)); +} + /* * deparse_expression - General utility for deparsing expressions diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 1e235c6..dc38532 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201212081 +#define CATALOG_VERSION_NO 201212261 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 010605d..64fbe7e 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -1964,6 +1964,8 @@ DATA(insert OID = 2232 ( pg_get_function_identity_arguments PGNSP PGUID 12 1 DESCR("identity argument list of a function"); DATA(insert OID = 2165 ( pg_get_function_result PGNSP PGUID 12 1 0 0 0 f f f f t f s 1 0 25 "26" _null_ _null_ _null_ _null_ pg_get_function_result _null_ _null_ _null_ )); DESCR("result type of a function"); +DATA(insert OID = 3839 ( pg_get_function_arg_default PGNSP PGUID 12 1 0 0 0 f f f f t f s 2 0 25 "26 23" _null_ _null_ _null_ _null_ pg_get_function_arg_default _null_ _null_ _null_ )); +DESCR("function argument default"); DATA(insert OID = 1686 ( pg_get_keywords PGNSP PGUID 12 10 400 0 0 f f f f t t s 0 0 2249 "" "{25,18,25}" "{o,o,o}" "{word,catcode,catdesc}" _null_ pg_get_keywords _null_ _null_ _null_ )); DESCR("list of SQL keywords"); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index 61d6aef..ed27116 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -652,6 +652,7 @@ extern Datum pg_get_functiondef(PG_FUNCTION_ARGS); extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS); extern Datum pg_get_function_identity_arguments(PG_FUNCTION_ARGS); extern Datum pg_get_function_result(PG_FUNCTION_ARGS); +extern Datum pg_get_function_arg_default(PG_FUNCTION_ARGS); extern char *deparse_expression(Node *expr, List *dpcontext, bool forceprefix, bool showimplicit); extern List *deparse_context_for(const char *aliasname, Oid relid); diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out index e795232..101274d 100644 --- a/src/test/regress/expected/create_function_3.out +++ b/src/test/regress/expected/create_function_3.out @@ -425,9 +425,22 @@ SELECT proname, proisstrict FROM pg_proc functext_f_4 | t (4 rows) +-- information_schema tests +CREATE FUNCTION functest1(a int, b int default 1, c text default 'foo') + RETURNS int + LANGUAGE SQL + AS 'SELECT $1 + $2'; +SELECT ordinal_position, parameter_name, parameter_default FROM information_schema.parameters WHERE specific_schema = 'temp_func_test' AND specific_name LIKE 'functest1%' ORDER BY 1; + ordinal_position | parameter_name | parameter_default +------------------+----------------+------------------- + 1 | a | + 2 | b | 1 + 3 | c | 'foo'::text +(3 rows) + -- Cleanups DROP SCHEMA temp_func_test CASCADE; -NOTICE: drop cascades to 16 other objects +NOTICE: drop cascades to 17 other objects DETAIL: drop cascades to function functest_a_1(text,date) drop cascades to function functest_a_2(text[]) drop cascades to function functest_a_3() @@ -444,5 +457,6 @@ drop cascades to function functext_f_1(integer) drop cascades to function functext_f_2(integer) drop cascades to function functext_f_3(integer) drop cascades to function functext_f_4(integer) +drop cascades to function functest1(integer,integer,text) DROP USER regtest_unpriv_user; RESET search_path; diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql index e2dd9a3..c02faad 100644 --- a/src/test/regress/sql/create_function_3.sql +++ b/src/test/regress/sql/create_function_3.sql @@ -138,6 +138,17 @@ CREATE FUNCTION functext_F_4(int) RETURNS bool LANGUAGE 'sql' 'functext_F_3'::regproc, 'functext_F_4'::regproc) ORDER BY proname; + +-- information_schema tests + +CREATE FUNCTION functest1(a int, b int default 1, c text default 'foo') + RETURNS int + LANGUAGE SQL + AS 'SELECT $1 + $2'; + +SELECT ordinal_position, parameter_name, parameter_default FROM information_schema.parameters WHERE specific_schema = 'temp_func_test' AND specific_name LIKE 'functest1%' ORDER BY 1; + + -- Cleanups DROP SCHEMA temp_func_test CASCADE; DROP USER regtest_unpriv_user;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers