Updated patch attached.
On Sat, 2013-11-09 at 12:09 +0530, Amit Khandekar wrote:
> > > 2) I found the following check a bit confusing, maybe you can make
> it
> > > better
> > > if (!argmodes || argmodes[i] == PROARGMODE_IN || argmodes[i] ==
> > > PROARGMODE_INOUT || argmodes[i] == PROARGMODE_VARIADIC)
> >
> > Factored that out into a separate helper function.
>
>
> The statement needs to be split into 80 columns width lines.
done
> > > 3) Function level comment for pg_get_function_arg_default() is
> > > missing.
> >
> > I think the purpose of the function is clear.
>
> Unless a reader goes through the definition, it is not obvious whether
> the second function argument represents the parameter position in
> input parameters or it is the parameter position in *all* the function
> parameters (i.e. proargtypes or proallargtypes). I think at least a
> one-liner description of what this function does should be present.
done
> >
> > > 4) You can also add comments inside the function, for example the
> > > comment for the line:
> > > nth = inputargn - 1 - (proc->pronargs - proc->pronargdefaults);
> >
> > Suggestion?
>
> Yes, it is difficult to understand what's the logic behind this
> calculation, until we go read the documentation related to
> pg_proc.proargdefaults. I think this should be sufficient:
> /*
> * proargdefaults elements always correspond to the last N input
> arguments,
> * where N = pronargdefaults. So calculate the nth_default accordingly.
> */
done
> There should be an initial check to see if nth_arg is passed a
> negative value. It is used as an index into the argmodes array, so a
> -ve array index can cause a crash. Because
> pg_get_function_arg_default() can now be called by a user also, we
> need to validate the input values. I am ok with returning with an
> error "Invalid argument".
done
> ---
> Instead of :
> deparse_expression_pretty(node, NIL, false, false, 0, 0)
> you can use :
> deparse_expression(node, NIL, false, false)
>
done
>From 442911934c1640af18a83ef2efaafc45c569c98f Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Thu, 14 Nov 2013 21:43:15 -0500
Subject: [PATCH] Implement information_schema.parameters.parameter_default
column
Reviewed-by: Ali Dar <ali.munir....@gmail.com>
Reviewed-by: Amit Khandekar <amit.khande...@enterprisedb.com>
---
doc/src/sgml/information_schema.sgml | 9 +++
src/backend/catalog/information_schema.sql | 9 ++-
src/backend/utils/adt/ruleutils.c | 84 +++++++++++++++++++++++++
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_proc.h | 2 +
src/include/utils/builtins.h | 1 +
src/test/regress/expected/create_function_3.out | 33 +++++++++-
src/test/regress/sql/create_function_3.sql | 24 +++++++
8 files changed, 160 insertions(+), 4 deletions(-)
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index 22e17bb..22f43c8 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 c5f7a8b..fd706e3 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1133,10 +1133,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 5ffce68..dace05a 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2266,6 +2266,90 @@ print_function_arguments(StringInfo buf, HeapTuple proctup,
return argsprinted;
}
+static bool
+is_input_argument(int nth, const char *argmodes)
+{
+ return (!argmodes
+ || argmodes[nth] == PROARGMODE_IN
+ || argmodes[nth] == PROARGMODE_INOUT
+ || argmodes[nth] == PROARGMODE_VARIADIC);
+}
+
+/*
+ * Get textual representation of a function argument's default value. The
+ * second argument of this function is the argument number among all arguments
+ * (i.e. proallargtypes, *not* proargtypes), starting with 1, because that's
+ * how information_schema.sql uses it.
+ */
+Datum
+pg_get_function_arg_default(PG_FUNCTION_ARGS)
+{
+ Oid funcid = PG_GETARG_OID(0);
+ int32 nth_arg = 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 nth_inputarg;
+ Datum proargdefaults;
+ bool isnull;
+ int nth_default;
+
+ 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 (nth_arg < 1 || nth_arg > numargs || !is_input_argument(nth_arg - 1, argmodes))
+ {
+ ReleaseSysCache(proctup);
+ PG_RETURN_NULL();
+ }
+
+ nth_inputarg = 0;
+ for (i = 0; i < nth_arg; i++)
+ if (is_input_argument(i, argmodes))
+ nth_inputarg++;
+
+ 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);
+
+ /* Calculate index into proargdefaults: proargdefaults corresponds to the
+ * last N input arguments, where N = pronargdefaults. */
+ nth_default = nth_inputarg - 1 - (proc->pronargs - proc->pronargdefaults);
+
+ if (nth_default < 0 || nth_default >= list_length(argdefaults))
+ {
+ ReleaseSysCache(proctup);
+ PG_RETURN_NULL();
+ }
+ node = list_nth(argdefaults, nth_default);
+ str = deparse_expression(node, NIL, false, false);
+
+ 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 4108f6c..d8e5af1 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201311081
+#define CATALOG_VERSION_NO 201311142
#endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index ca4fc62..39b07e2 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1973,6 +1973,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 = 3846 ( 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 ce3f00b..85f475b 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -665,6 +665,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..486ae7a 100644
--- a/src/test/regress/expected/create_function_3.out
+++ b/src/test/regress/expected/create_function_3.out
@@ -425,9 +425,37 @@ SELECT proname, proisstrict FROM pg_proc
functext_f_4 | t
(4 rows)
+-- information_schema tests
+CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
+ RETURNS int
+ LANGUAGE SQL
+ AS 'SELECT $1 + $2';
+CREATE FUNCTION functest_IS_2(out a int, b int default 1)
+ RETURNS int
+ LANGUAGE SQL
+ AS 'SELECT $1';
+CREATE FUNCTION functest_IS_3(a int default 1, out b int)
+ RETURNS int
+ LANGUAGE SQL
+ AS 'SELECT $1';
+SELECT routine_name, ordinal_position, parameter_name, parameter_default
+ FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name)
+ WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_'
+ ORDER BY 1, 2;
+ routine_name | ordinal_position | parameter_name | parameter_default
+---------------+------------------+----------------+-------------------
+ functest_is_1 | 1 | a |
+ functest_is_1 | 2 | b | 1
+ functest_is_1 | 3 | c | 'foo'::text
+ functest_is_2 | 1 | a |
+ functest_is_2 | 2 | b | 1
+ functest_is_3 | 1 | a | 1
+ functest_is_3 | 2 | b |
+(7 rows)
+
-- Cleanups
DROP SCHEMA temp_func_test CASCADE;
-NOTICE: drop cascades to 16 other objects
+NOTICE: drop cascades to 19 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 +472,8 @@ 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 functest_is_1(integer,integer,text)
+drop cascades to function functest_is_2(integer)
+drop cascades to function functest_is_3(integer)
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..54b25e6 100644
--- a/src/test/regress/sql/create_function_3.sql
+++ b/src/test/regress/sql/create_function_3.sql
@@ -138,6 +138,30 @@ 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 functest_IS_1(a int, b int default 1, c text default 'foo')
+ RETURNS int
+ LANGUAGE SQL
+ AS 'SELECT $1 + $2';
+
+CREATE FUNCTION functest_IS_2(out a int, b int default 1)
+ RETURNS int
+ LANGUAGE SQL
+ AS 'SELECT $1';
+
+CREATE FUNCTION functest_IS_3(a int default 1, out b int)
+ RETURNS int
+ LANGUAGE SQL
+ AS 'SELECT $1';
+
+SELECT routine_name, ordinal_position, parameter_name, parameter_default
+ FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name)
+ WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_'
+ ORDER BY 1, 2;
+
+
-- Cleanups
DROP SCHEMA temp_func_test CASCADE;
DROP USER regtest_unpriv_user;
--
1.8.4.2
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
