Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit85ed91e

Browse files
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 parent3803ff9 commit85ed91e

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(nullAS sql_identifier)AS scope_schema,
11341134
CAST(nullAS sql_identifier)AS scope_name,
11351135
CAST(nullAS cardinal_number)AS maximum_cardinality,
1136-
CAST((ss.x).nAS sql_identifier)AS dtd_identifier
1136+
CAST((ss.x).nAS 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+
ELSENULL END
1141+
AS character_data)AS parameter_default
11371142

11381143
FROM pg_type t, pg_namespace nt,
1139-
(SELECTn.nspnameAS n_nspname,p.proname,p.oidAS p_oid,
1144+
(SELECTn.nspnameAS n_nspname,p.proname,p.oidAS 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
returnargsprinted;
22672267
}
22682268

2269+
staticbool
2270+
is_input_argument(intnth,constchar*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+
Oidfuncid=PG_GETARG_OID(0);
2288+
int32nth_arg=PG_GETARG_INT32(1);
2289+
HeapTupleproctup;
2290+
Form_pg_procproc;
2291+
intnumargs;
2292+
Oid*argtypes;
2293+
char**argnames;
2294+
char*argmodes;
2295+
inti;
2296+
List*argdefaults;
2297+
Node*node;
2298+
char*str;
2299+
intnth_inputarg;
2300+
Datumproargdefaults;
2301+
boolisnull;
2302+
intnth_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-
#defineCATALOG_VERSION_NO201311211
56+
#defineCATALOG_VERSION_NO201311261
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(insertOID=2165 (pg_get_function_resultPGNSPPGUID121000fffftfs1025"26"_null__null__null__null_pg_get_function_result_null__null__null_ ));
19751975
DESCR("result type of a function");
1976+
DATA(insertOID=3808 (pg_get_function_arg_defaultPGNSPPGUID121000fffftfs2025"26 23"_null__null__null__null_pg_get_function_arg_default_null__null__null_ ));
1977+
DESCR("function argument default");
19761978

19771979
DATA(insertOID=1686 (pg_get_keywordsPGNSPPGUID121040000fffftts002249"""{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
externDatumpg_get_function_arguments(PG_FUNCTION_ARGS);
666666
externDatumpg_get_function_identity_arguments(PG_FUNCTION_ARGS);
667667
externDatumpg_get_function_result(PG_FUNCTION_ARGS);
668+
externDatumpg_get_function_arg_default(PG_FUNCTION_ARGS);
668669
externchar*deparse_expression(Node*expr,List*dpcontext,
669670
boolforceprefix,boolshowimplicit);
670671
externList*deparse_context_for(constchar*aliasname,Oidrelid);

‎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 to16 other objects
458+
NOTICE: drop cascades to19 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+
CREATEFUNCTIONfunctest_IS_1(aint, bint default1, ctext default'foo')
145+
RETURNSint
146+
LANGUAGE SQL
147+
AS'SELECT $1 + $2';
148+
149+
CREATEFUNCTIONfunctest_IS_2(out aint, bint default1)
150+
RETURNSint
151+
LANGUAGE SQL
152+
AS'SELECT $1';
153+
154+
CREATEFUNCTIONfunctest_IS_3(aint default1, out bint)
155+
RETURNSint
156+
LANGUAGE SQL
157+
AS'SELECT $1';
158+
159+
SELECT routine_name, ordinal_position, parameter_name, parameter_default
160+
FROMinformation_schema.parametersJOINinformation_schema.routines USING (specific_schema, specific_name)
161+
WHERE routine_schema='temp_func_test'AND routine_name ~'^functest_is_'
162+
ORDER BY1,2;
163+
164+
141165
-- Cleanups
142166
DROPSCHEMA temp_func_test CASCADE;
143167
DROPUSER regtest_unpriv_user;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp