Re: information schema parameter_default implementation - Mailing list pgsql-hackers

Here is an updated patch which fixes the bug you have pointed out.

On Thu, 2013-01-31 at 18:59 +0500, Ali Dar wrote:

> I checked our your patch. There seems to be an issue when we have OUT
> parameters after the DEFAULT values.

Fixed.

> Some other minor observations:
> 1) Some variables are not lined in pg_get_function_arg_default().

Are you referring to indentation issues?  I think the indentation is
good, but pgindent will fix it anyway.

> 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.
>
> 2) inputargn can be assigned in declaration.

I'd prefer to initialize it close to where it is used.

> 3) Function level comment for pg_get_function_arg_default() is
> missing.

I think the purpose of the function is clear.

> 4) You can also add comments inside the function, for example the
> comment for the line:
> nth = inputargn - 1 - (proc->pronargs - proc->pronargdefaults);

Suggestion?

> 5) I think the line added in the
> documentation(informational_schema.sgml) is very long. Consider
> revising. Maybe change from:
>
> "The default expression of the parameter, or null if none or if the
> function is not owned by a currently enabled role." TO
>
> "The default expression of the parameter, or null if none was
> specified. It will also be null if the function is not owned by a
> currently enabled role."
>
> I don't know what do you exactly mean by: "function is not owned by a
> currently enabled role"?

I think this style is used throughout the documentation of the
information schema.  We need to keep the descriptions reasonably
compact, but I'm willing to entertain other opinions.


>From 36f25fa2ec96879bda1993818db9a9632d8ac340 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Sat, 14 Sep 2013 15:55:54 -0400
Subject: [PATCH] Implement information_schema.parameters.parameter_default
 column

Reviewed-by: Ali Dar <ali.munir.dar@gmail.com>
---
 doc/src/sgml/information_schema.sgml            |  9 ++++
 src/backend/catalog/information_schema.sql      |  9 +++-
 src/backend/utils/adt/ruleutils.c               | 72 +++++++++++++++++++++++++
 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, 148 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 9a1d12e..5a05396 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -2265,6 +2265,78 @@ static char *generate_function_name(Oid funcid, int nargs,
     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);
+}
+
+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 > 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);
+
+    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_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 3a18935..a0a9b4c 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
  */

 /*                            yyyymmddN */
-#define CATALOG_VERSION_NO    201309051
+#define CATALOG_VERSION_NO    201309112

 #endif
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index f03dd0b..5a5407c 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 = 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 a5a0561..540bd0d 100644
--- a/src/include/utils/builtins.h
+++ b/src/include/utils/builtins.h
@@ -658,6 +658,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.rc3