Skip to content

Commit b329d7c

Browse files
Iam-WenYiIam-WenYi
authored
OSPP 2024: Improve pg_get_functiondef (#684)
* Add the pg_get_functiondef extension * Improve the pg_get_functiondef extension * update pg_get_functiondef * update the pg_get_functiondef * Fix syntax error * Fix the syntax error * Improve comment * Fix regression testing bug * update pg_get_functiondef * Improve code --------- Co-authored-by: Wen Yi <[email protected]>
1 parent 510fd2a commit b329d7c

10 files changed

Lines changed: 527 additions & 0 deletions

‎contrib/Makefile‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ SUBDIRS = \
3333
passwordcheck \
3434
pg_buffercache \
3535
pg_freespacemap \
36+
pg_get_functiondef \
3637
pg_prewarm \
3738
pg_stat_statements \
3839
pg_surgery \

‎contrib/pg_get_functiondef/Makefile‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
1+
# contrib/pg_get_functiondef/Makefile
2+
3+
MODULE_big = pg_get_functiondef
4+
OBJS = \
5+
$(WIN32RES) \
6+
pg_get_functiondef_no_input.o \
7+
pg_get_functiondef_mul.o \
8+
pg_get_functiondef_extend.o
9+
10+
EXTENSION = pg_get_functiondef
11+
DATA = pg_get_functiondef--1.0.sql
12+
PGFILEDESC = "pg_get_functiondef - Enhancement of existing pg_get_functiondef"
13+
14+
REGRESS = pg_get_functiondef
15+
ORA_REGRESS = pg_get_functiondef
16+
17+
ifdef USE_PGXS
18+
PG_CONFIG = pg_config
19+
PGXS := $(shell $(PG_CONFIG) --pgxs)
20+
include $(PGXS)
21+
else
22+
subdir = contrib/pg_get_functiondef
23+
top_builddir = ../..
24+
include $(top_builddir)/src/Makefile.global
25+
include $(top_srcdir)/contrib/contrib-global.mk
26+
endif

‎contrib/pg_get_functiondef/expected/pg_get_functiondef.out‎

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,99 @@
1+
CREATE EXTENSION pg_get_functiondef;
2+
SELECT pg_get_functiondef();
3+
ERROR: Nothing Input
4+
DETAIL: SELECT pg_get_functiondef(target, ...);
5+
HINT: target should be the name or the oid of the function.
6+
SELECT pg_get_functiondef(1, 2, 3);
7+
pg_get_functiondef
8+
--------------------------------------------------------------------------
9+
(1,"")
10+
(2,"")
11+
(3,"CREATE OR REPLACE FUNCTION pg_catalog.heap_tableam_handler(internal)+
12+
RETURNS table_am_handler +
13+
LANGUAGE internal +
14+
PARALLEL SAFE STRICT +
15+
AS $function$heap_tableam_handler$function$ +
16+
")
17+
(3 rows)
18+
19+
SELECT * FROM pg_get_functiondef(1, 2, 3);
20+
oid | pg_get_functiondef
21+
-----+----------------------------------------------------------------------
22+
1 |
23+
2 |
24+
3 | CREATE OR REPLACE FUNCTION pg_catalog.heap_tableam_handler(internal)+
25+
| RETURNS table_am_handler +
26+
| LANGUAGE internal +
27+
| PARALLEL SAFE STRICT +
28+
| AS $function$heap_tableam_handler$function$ +
29+
|
30+
(3 rows)
31+
32+
SELECT pg_get_functiondef('char_length', 'substring');
33+
pg_get_functiondef
34+
------------------------------------------------------------------------------
35+
(char_length,"/* oid: 1381 */ +
36+
CREATE OR REPLACE FUNCTION pg_catalog.char_length(text) +
37+
RETURNS integer +
38+
LANGUAGE internal +
39+
IMMUTABLE PARALLEL SAFE STRICT +
40+
AS $function$textlen$function$ +
41+
/* oid: 1372 */ +
42+
CREATE OR REPLACE FUNCTION pg_catalog.char_length(character) +
43+
RETURNS integer +
44+
LANGUAGE internal +
45+
IMMUTABLE PARALLEL SAFE STRICT +
46+
AS $function$bpcharlen$function$ +
47+
")
48+
(substring,"/* oid: 2013 */ +
49+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(bytea, integer) +
50+
RETURNS bytea +
51+
LANGUAGE internal +
52+
IMMUTABLE PARALLEL SAFE STRICT +
53+
AS $function$bytea_substr_no_len$function$ +
54+
/* oid: 937 */ +
55+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, integer) +
56+
RETURNS text +
57+
LANGUAGE internal +
58+
IMMUTABLE PARALLEL SAFE STRICT +
59+
AS $function$text_substr_no_len$function$ +
60+
/* oid: 2073 */ +
61+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, text) +
62+
RETURNS text +
63+
LANGUAGE internal +
64+
IMMUTABLE PARALLEL SAFE STRICT +
65+
AS $function$textregexsubstr$function$ +
66+
/* oid: 1699 */ +
67+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(bit, integer) +
68+
RETURNS bit +
69+
LANGUAGE internal +
70+
IMMUTABLE PARALLEL SAFE STRICT +
71+
AS $function$bitsubstr_no_len$function$ +
72+
/* oid: 2012 */ +
73+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(bytea, integer, integer)+
74+
RETURNS bytea +
75+
LANGUAGE internal +
76+
IMMUTABLE PARALLEL SAFE STRICT +
77+
AS $function$bytea_substr$function$ +
78+
/* oid: 936 */ +
79+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, integer, integer) +
80+
RETURNS text +
81+
LANGUAGE internal +
82+
IMMUTABLE PARALLEL SAFE STRICT +
83+
AS $function$text_substr$function$ +
84+
/* oid: 2074 */ +
85+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, text, text) +
86+
RETURNS text +
87+
LANGUAGE sql +
88+
IMMUTABLE PARALLEL SAFE STRICT COST 1 +
89+
RETURN ""substring""($1, similar_to_escape($2, $3)) +
90+
/* oid: 1680 */ +
91+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(bit, integer, integer) +
92+
RETURNS bit +
93+
LANGUAGE internal +
94+
IMMUTABLE PARALLEL SAFE STRICT +
95+
AS $function$bitsubstr$function$ +
96+
")
97+
(2 rows)
98+
99+
DROP EXTENSION pg_get_functiondef;

‎contrib/pg_get_functiondef/expected/pg_get_functiondef_1.out‎

Lines changed: 99 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,99 @@
1+
CREATE EXTENSION pg_get_functiondef;
2+
SELECT pg_get_functiondef();
3+
ERROR: Nothing Input
4+
DETAIL: SELECT pg_get_functiondef(target, ...);
5+
HINT: target should be the name or the oid of the function.
6+
SELECT pg_get_functiondef(1, 2, 3);
7+
pg_get_functiondef
8+
--------------------------------------------------------------------------
9+
(1,"")
10+
(2,"")
11+
(3,"CREATE OR REPLACE FUNCTION pg_catalog.heap_tableam_handler(internal)+
12+
RETURNS table_am_handler +
13+
LANGUAGE internal +
14+
PARALLEL SAFE STRICT +
15+
AS $function$heap_tableam_handler$function$ +
16+
")
17+
(3 rows)
18+
19+
SELECT * FROM pg_get_functiondef(1, 2, 3);
20+
oid | pg_get_functiondef
21+
-----+----------------------------------------------------------------------
22+
1 |
23+
2 |
24+
3 | CREATE OR REPLACE FUNCTION pg_catalog.heap_tableam_handler(internal)+
25+
| RETURNS table_am_handler +
26+
| LANGUAGE internal +
27+
| PARALLEL SAFE STRICT +
28+
| AS $function$heap_tableam_handler$function$ +
29+
|
30+
(3 rows)
31+
32+
SELECT pg_get_functiondef('char_length', 'substring');
33+
pg_get_functiondef
34+
-------------------------------------------------------------------------------------------------------
35+
(char_length,"/* oid: 1381 */ +
36+
CREATE OR REPLACE FUNCTION pg_catalog.char_length(text) +
37+
RETURNS pg_catalog.int4 +
38+
LANGUAGE internal +
39+
IMMUTABLE PARALLEL SAFE STRICT +
40+
AS $function$textlen$function$ +
41+
/* oid: 1372 */ +
42+
CREATE OR REPLACE FUNCTION pg_catalog.char_length(pg_catalog.bpchar) +
43+
RETURNS pg_catalog.int4 +
44+
LANGUAGE internal +
45+
IMMUTABLE PARALLEL SAFE STRICT +
46+
AS $function$bpcharlen$function$ +
47+
")
48+
(substring,"/* oid: 2013 */ +
49+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(bytea, pg_catalog.int4) +
50+
RETURNS bytea +
51+
LANGUAGE internal +
52+
IMMUTABLE PARALLEL SAFE STRICT +
53+
AS $function$bytea_substr_no_len$function$ +
54+
/* oid: 937 */ +
55+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, pg_catalog.int4) +
56+
RETURNS text +
57+
LANGUAGE internal +
58+
IMMUTABLE PARALLEL SAFE STRICT +
59+
AS $function$text_substr_no_len$function$ +
60+
/* oid: 2073 */ +
61+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, text) +
62+
RETURNS text +
63+
LANGUAGE internal +
64+
IMMUTABLE PARALLEL SAFE STRICT +
65+
AS $function$textregexsubstr$function$ +
66+
/* oid: 1699 */ +
67+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(pg_catalog.bit, pg_catalog.int4) +
68+
RETURNS pg_catalog.bit +
69+
LANGUAGE internal +
70+
IMMUTABLE PARALLEL SAFE STRICT +
71+
AS $function$bitsubstr_no_len$function$ +
72+
/* oid: 2012 */ +
73+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(bytea, pg_catalog.int4, pg_catalog.int4) +
74+
RETURNS bytea +
75+
LANGUAGE internal +
76+
IMMUTABLE PARALLEL SAFE STRICT +
77+
AS $function$bytea_substr$function$ +
78+
/* oid: 936 */ +
79+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, pg_catalog.int4, pg_catalog.int4) +
80+
RETURNS text +
81+
LANGUAGE internal +
82+
IMMUTABLE PARALLEL SAFE STRICT +
83+
AS $function$text_substr$function$ +
84+
/* oid: 2074 */ +
85+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(text, text, text) +
86+
RETURNS text +
87+
LANGUAGE sql +
88+
IMMUTABLE PARALLEL SAFE STRICT COST 1 +
89+
RETURN ""substring""($1, similar_to_escape($2, $3)) +
90+
/* oid: 1680 */ +
91+
CREATE OR REPLACE FUNCTION pg_catalog.""substring""(pg_catalog.bit, pg_catalog.int4, pg_catalog.int4)+
92+
RETURNS pg_catalog.bit +
93+
LANGUAGE internal +
94+
IMMUTABLE PARALLEL SAFE STRICT +
95+
AS $function$bitsubstr$function$ +
96+
")
97+
(2 rows)
98+
99+
DROP EXTENSION pg_get_functiondef;

‎contrib/pg_get_functiondef/pg_get_functiondef--1.0.sql‎

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,13 @@
1+
/* contrib/pg_get_functiondef/pg_get_functiondef--1.0.sql */
2+
3+
CREATE FUNCTION pg_get_functiondef() RETURNS CSTRING
4+
AS 'pg_get_functiondef', 'pg_get_functiondef_no_input'
5+
LANGUAGE C;
6+
7+
CREATE FUNCTION pg_get_functiondef(OID, VARIADIC OID[]) RETURNS TABLE (oid OID, pg_get_functiondef CSTRING)
8+
AS 'pg_get_functiondef', 'pg_get_functiondef_mul'
9+
LANGUAGE C;
10+
11+
CREATE FUNCTION pg_get_functiondef(VARIADIC TEXT[]) RETURNS TABLE (name CSTRING, pg_get_functiondef CSTRING)
12+
AS 'pg_get_functiondef', 'pg_get_functiondef_extend'
13+
LANGUAGE C;

‎contrib/pg_get_functiondef/pg_get_functiondef.control‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
# pg_get_functiondef extension
2+
comment = 'Get function\'s definition'
3+
default_version = '1.0'
4+
module_pathname = '$libdir/pg_get_functiondef'
5+
relocatable = false
6+
trusted = true

‎contrib/pg_get_functiondef/pg_get_functiondef_extend.c‎

Lines changed: 127 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,127 @@
1+
/*
2+
pg_get_functiondef_extend.c
3+
The implementation of pg_get_functiondef('function_name', 'function_name', ...)
4+
*/
5+
#include "postgres.h"
6+
#include "fmgr.h"
7+
#include "utils/elog.h"
8+
#include "lib/stringinfo.h"
9+
#include "executor/spi.h"
10+
#include "utils/array.h"
11+
#include "catalog/pg_proc.h"
12+
#include "catalog/pg_type_d.h"
13+
#include "funcapi.h"
14+
#include "utils/builtins.h"
15+
#include "utils/catcache.h"
16+
#include "utils/lsyscache.h"
17+
#include "utils/syscache.h"
18+
#include "access/htup_details.h"
19+
20+
typedef struct FunctionInfoData *FunctionInfo;
21+
struct FunctionInfoData
22+
{
23+
char **function_name; /* The user's input */
24+
int cursor; /* The cursor of the function_name[] */
25+
int count_of_tuples; /* How many tuples will return? */
26+
TupleDesc result_desc; /* Describe the tuple */
27+
AttInMetadata *result_tuple_meta; /* Describe the tuple's attribute */
28+
};
29+
30+
/*
31+
GetFuncDefByName
32+
Export the oid based on the function name, the use the oid get the definition
33+
*/
34+
char *GetFuncDefByName(char *function_name);
35+
char *GetFuncDefByName(char *function_name)
36+
{
37+
CatCList *catlist = NULL;
38+
StringInfoData result = {};
39+
initStringInfo(&result);
40+
catlist = SearchSysCacheList1(PROCNAMEARGSNSP, CStringGetDatum(function_name));
41+
42+
if (catlist->n_members == 0)
43+
{
44+
appendStringInfo(&result, "/* Not Found */");
45+
}
46+
else
47+
{
48+
/* A function name may correspond to multiple different results */
49+
for (int i = 0; i < catlist->n_members; i++)
50+
{
51+
HeapTuple proctup = &catlist->members[i]->tuple;
52+
Form_pg_proc proc = (Form_pg_proc)GETSTRUCT(proctup);
53+
if (proc->prokind == PROKIND_AGGREGATE)
54+
{
55+
appendStringInfo(&result, "/* Not support aggregate function, oid: %d */\n", proc->oid);
56+
continue;
57+
}
58+
appendStringInfo(&result, "/* oid: %d */\n", proc->oid);
59+
appendStringInfo(&result, "%s", text_to_cstring(DatumGetTextP(DirectFunctionCall1(pg_get_functiondef, proc->oid))));
60+
}
61+
}
62+
ReleaseCatCacheList(catlist);
63+
return result.data;
64+
}
65+
66+
PG_FUNCTION_INFO_V1(pg_get_functiondef_extend);
67+
68+
Datum pg_get_functiondef_extend(PG_FUNCTION_ARGS)
69+
{
70+
FuncCallContext *funcctx = NULL;
71+
FunctionInfo info = NULL;
72+
73+
if (SRF_IS_FIRSTCALL())
74+
{
75+
ArrayType *arguments_raw;
76+
int count_of_arguments;
77+
Datum *arguments;
78+
MemoryContext old_context;
79+
80+
funcctx = SRF_FIRSTCALL_INIT();
81+
old_context = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
82+
83+
arguments_raw = PG_GETARG_ARRAYTYPE_P(0);
84+
deconstruct_array_builtin((ArrayType *)arguments_raw, TEXTOID, &arguments, NULL, &count_of_arguments);
85+
86+
info = (FunctionInfo)palloc0(sizeof(struct FunctionInfoData));
87+
funcctx->user_fctx = info;
88+
89+
info->function_name = palloc0(count_of_arguments * sizeof(char *));
90+
for (int i = 0; i < count_of_arguments; i++)
91+
{
92+
info->function_name[i] = text_to_cstring((text *)arguments[i]);
93+
}
94+
95+
info->cursor = 0;
96+
info->count_of_tuples = count_of_arguments;
97+
98+
if (get_call_result_type(fcinfo, NULL, &info->result_desc) != TYPEFUNC_COMPOSITE)
99+
{
100+
ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("function returning record called in context that cannot accept type record")));
101+
}
102+
info->result_tuple_meta = TupleDescGetAttInMetadata(info->result_desc);
103+
MemoryContextSwitchTo(old_context);
104+
}
105+
106+
funcctx = SRF_PERCALL_SETUP();
107+
info = (FunctionInfo)(funcctx->user_fctx);
108+
109+
if (info->cursor == info->count_of_tuples)
110+
{
111+
SRF_RETURN_DONE(funcctx);
112+
}
113+
else
114+
{
115+
HeapTuple tuple;
116+
Datum tuple_return;
117+
char **values;
118+
values = palloc0(sizeof(char *) * 2);
119+
values[0] = pstrdup(info->function_name[info->cursor]);
120+
values[1] = GetFuncDefByName(info->function_name[info->cursor]);
121+
info->cursor++;
122+
tuple = BuildTupleFromCStrings(info->result_tuple_meta, values);
123+
tuple_return = HeapTupleGetDatum(tuple);
124+
SRF_RETURN_NEXT(funcctx, tuple_return);
125+
}
126+
PG_RETURN_NULL();
127+
}

0 commit comments

Comments
 (0)