Skip to content

Commit 85ed91e

Browse files
peterepetere
committed
Implement information_schema.parameters.parameter_default column
Reviewed-by: Ali Dar <ali.munir.dar@gmail.com> Reviewed-by: Amit Khandekar <amit.khandekar@enterprisedb.com> Reviewed-by: Rodolfo Campero <rodolfo.campero@anachronics.com>
1 parent 3803ff9 commit 85ed91e

File tree

8 files changed

+160
-4
lines changed

8 files changed

+160
-4
lines changed

‎doc/src/sgml/information_schema.sgml

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3323,6 +3323,15 @@ ORDER BY c.ordinal_position;
33233323
in future versions.)
33243324
</entry>
33253325
</row>
3326+
3327+
<row>
3328+
<entry><literal>parameter_default</literal></entry>
3329+
<entry><type>character_data</type></entry>
3330+
<entry>
3331+
The default expression of the parameter, or null if none or if the
3332+
function is not owned by a currently enabled role.
3333+
</entry>
3334+
</row>
33263335
</tbody>
33273336
</tgroup>
33283337
</table>

‎src/backend/catalog/information_schema.sql

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1133,10 +1133,15 @@ CREATE VIEW parameters AS
11331133
CAST(null AS sql_identifier) AS scope_schema,
11341134
CAST(null AS sql_identifier) AS scope_name,
11351135
CAST(null AS cardinal_number) AS maximum_cardinality,
1136-
CAST((ss.x).n AS sql_identifier) AS dtd_identifier
1136+
CAST((ss.x).n AS sql_identifier) AS dtd_identifier,
1137+
CAST(
1138+
CASE WHEN pg_has_role(proowner, 'USAGE')
1139+
THEN pg_get_function_arg_default(p_oid, (ss.x).n)
1140+
ELSE NULL END
1141+
AS character_data) AS parameter_default
11371142

11381143
FROM pg_type t, pg_namespace nt,
1139-
(SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid,
1144+
(SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner,
11401145
p.proargnames, p.proargmodes,
11411146
_pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x
11421147
FROM pg_namespace n, pg_proc p

‎src/backend/utils/adt/ruleutils.c

Lines changed: 84 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2266,6 +2266,90 @@ print_function_arguments(StringInfo buf, HeapTuple proctup,
22662266
return argsprinted;
22672267
}
22682268

2269+
static bool
2270+
is_input_argument(int nth, const char *argmodes)
2271+
{
2272+
return (!argmodes
2273+
|| argmodes[nth] == PROARGMODE_IN
2274+
|| argmodes[nth] == PROARGMODE_INOUT
2275+
|| argmodes[nth] == PROARGMODE_VARIADIC);
2276+
}
2277+
2278+
/*
2279+
* Get textual representation of a function argument's default value. The
2280+
* second argument of this function is the argument number among all arguments
2281+
* (i.e. proallargtypes, *not* proargtypes), starting with 1, because that's
2282+
* how information_schema.sql uses it.
2283+
*/
2284+
Datum
2285+
pg_get_function_arg_default(PG_FUNCTION_ARGS)
2286+
{
2287+
Oid funcid = PG_GETARG_OID(0);
2288+
int32 nth_arg = PG_GETARG_INT32(1);
2289+
HeapTuple proctup;
2290+
Form_pg_proc proc;
2291+
int numargs;
2292+
Oid *argtypes;
2293+
char **argnames;
2294+
char *argmodes;
2295+
int i;
2296+
List *argdefaults;
2297+
Node *node;
2298+
char *str;
2299+
int nth_inputarg;
2300+
Datum proargdefaults;
2301+
bool isnull;
2302+
int nth_default;
2303+
2304+
proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid));
2305+
if (!HeapTupleIsValid(proctup))
2306+
elog(ERROR, "cache lookup failed for function %u", funcid);
2307+
2308+
numargs = get_func_arg_info(proctup, &argtypes, &argnames, &argmodes);
2309+
if (nth_arg < 1 || nth_arg > numargs || !is_input_argument(nth_arg - 1, argmodes))
2310+
{
2311+
ReleaseSysCache(proctup);
2312+
PG_RETURN_NULL();
2313+
}
2314+
2315+
nth_inputarg = 0;
2316+
for (i = 0; i < nth_arg; i++)
2317+
if (is_input_argument(i, argmodes))
2318+
nth_inputarg++;
2319+
2320+
proargdefaults = SysCacheGetAttr(PROCOID, proctup,
2321+
Anum_pg_proc_proargdefaults,
2322+
&isnull);
2323+
if (isnull)
2324+
{
2325+
ReleaseSysCache(proctup);
2326+
PG_RETURN_NULL();
2327+
}
2328+
2329+
str = TextDatumGetCString(proargdefaults);
2330+
argdefaults = (List *) stringToNode(str);
2331+
Assert(IsA(argdefaults, List));
2332+
pfree(str);
2333+
2334+
proc = (Form_pg_proc) GETSTRUCT(proctup);
2335+
2336+
/* Calculate index into proargdefaults: proargdefaults corresponds to the
2337+
* last N input arguments, where N = pronargdefaults. */
2338+
nth_default = nth_inputarg - 1 - (proc->pronargs - proc->pronargdefaults);
2339+
2340+
if (nth_default < 0 || nth_default >= list_length(argdefaults))
2341+
{
2342+
ReleaseSysCache(proctup);
2343+
PG_RETURN_NULL();
2344+
}
2345+
node = list_nth(argdefaults, nth_default);
2346+
str = deparse_expression(node, NIL, false, false);
2347+
2348+
ReleaseSysCache(proctup);
2349+
2350+
PG_RETURN_TEXT_P(string_to_text(str));
2351+
}
2352+
22692353

22702354
/*
22712355
* deparse_expression - General utility for deparsing expressions

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201311211
56+
#define CATALOG_VERSION_NO 201311261
5757

5858
#endif

‎src/include/catalog/pg_proc.h

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1973,6 +1973,8 @@ DATA(insert OID = 2232 ( pg_get_function_identity_arguments PGNSP PGUID 12 1
19731973
DESCR("identity argument list of a function");
19741974
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_ ));
19751975
DESCR("result type of a function");
1976+
DATA(insert OID = 3808 ( 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_ ));
1977+
DESCR("function argument default");
19761978

19771979
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_ ));
19781980
DESCR("list of SQL keywords");

‎src/include/utils/builtins.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -665,6 +665,7 @@ extern Datum pg_get_functiondef(PG_FUNCTION_ARGS);
665665
extern Datum pg_get_function_arguments(PG_FUNCTION_ARGS);
666666
extern Datum pg_get_function_identity_arguments(PG_FUNCTION_ARGS);
667667
extern Datum pg_get_function_result(PG_FUNCTION_ARGS);
668+
extern Datum pg_get_function_arg_default(PG_FUNCTION_ARGS);
668669
extern char *deparse_expression(Node *expr, List *dpcontext,
669670
bool forceprefix, bool showimplicit);
670671
extern List *deparse_context_for(const char *aliasname, Oid relid);

‎src/test/regress/expected/create_function_3.out

Lines changed: 32 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -425,9 +425,37 @@ SELECT proname, proisstrict FROM pg_proc
425425
functext_f_4 | t
426426
(4 rows)
427427

428+
-- information_schema tests
429+
CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
430+
RETURNS int
431+
LANGUAGE SQL
432+
AS 'SELECT $1 + $2';
433+
CREATE FUNCTION functest_IS_2(out a int, b int default 1)
434+
RETURNS int
435+
LANGUAGE SQL
436+
AS 'SELECT $1';
437+
CREATE FUNCTION functest_IS_3(a int default 1, out b int)
438+
RETURNS int
439+
LANGUAGE SQL
440+
AS 'SELECT $1';
441+
SELECT routine_name, ordinal_position, parameter_name, parameter_default
442+
FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name)
443+
WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_'
444+
ORDER BY 1, 2;
445+
routine_name | ordinal_position | parameter_name | parameter_default
446+
---------------+------------------+----------------+-------------------
447+
functest_is_1 | 1 | a |
448+
functest_is_1 | 2 | b | 1
449+
functest_is_1 | 3 | c | 'foo'::text
450+
functest_is_2 | 1 | a |
451+
functest_is_2 | 2 | b | 1
452+
functest_is_3 | 1 | a | 1
453+
functest_is_3 | 2 | b |
454+
(7 rows)
455+
428456
-- Cleanups
429457
DROP SCHEMA temp_func_test CASCADE;
430-
NOTICE: drop cascades to 16 other objects
458+
NOTICE: drop cascades to 19 other objects
431459
DETAIL: drop cascades to function functest_a_1(text,date)
432460
drop cascades to function functest_a_2(text[])
433461
drop cascades to function functest_a_3()
@@ -444,5 +472,8 @@ drop cascades to function functext_f_1(integer)
444472
drop cascades to function functext_f_2(integer)
445473
drop cascades to function functext_f_3(integer)
446474
drop cascades to function functext_f_4(integer)
475+
drop cascades to function functest_is_1(integer,integer,text)
476+
drop cascades to function functest_is_2(integer)
477+
drop cascades to function functest_is_3(integer)
447478
DROP USER regtest_unpriv_user;
448479
RESET search_path;

‎src/test/regress/sql/create_function_3.sql

Lines changed: 24 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -138,6 +138,30 @@ SELECT proname, proisstrict FROM pg_proc
138138
'functext_F_3'::regproc,
139139
'functext_F_4'::regproc) ORDER BY proname;
140140

141+
142+
-- information_schema tests
143+
144+
CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo')
145+
RETURNS int
146+
LANGUAGE SQL
147+
AS 'SELECT $1 + $2';
148+
149+
CREATE FUNCTION functest_IS_2(out a int, b int default 1)
150+
RETURNS int
151+
LANGUAGE SQL
152+
AS 'SELECT $1';
153+
154+
CREATE FUNCTION functest_IS_3(a int default 1, out b int)
155+
RETURNS int
156+
LANGUAGE SQL
157+
AS 'SELECT $1';
158+
159+
SELECT routine_name, ordinal_position, parameter_name, parameter_default
160+
FROM information_schema.parameters JOIN information_schema.routines USING (specific_schema, specific_name)
161+
WHERE routine_schema = 'temp_func_test' AND routine_name ~ '^functest_is_'
162+
ORDER BY 1, 2;
163+
164+
141165
-- Cleanups
142166
DROP SCHEMA temp_func_test CASCADE;
143167
DROP USER regtest_unpriv_user;

0 commit comments

Comments
 (0)