**
PG获取存储过程及函数内容
**
函数:
SELECT ‘CREATE OR REPLACE FUNCTION xxx’||‘(’||chr(10)|| REPLACE(pg_catalog.pg_get_function_arguments(a.oid),‘,’,‘,’||chr(10))||chr(10)||‘)’
||chr(10)||'RETURNS ‘||
pg_catalog.pg_get_function_result(a.oid)||’ AS ‘||
a.prosrc ||’
′
∣
∣
c
h
r
(
10
)
∣
∣
′
L
A
N
G
U
A
G
E
p
l
p
g
s
q
l
′
F
R
O
M
p
g
c
a
t
a
l
o
g
.
p
g
p
r
o
c
a
J
O
I
N
p
g
c
a
t
a
l
o
g
.
p
g
n
a
m
e
s
p
a
c
e
b
O
N
a
.
p
r
o
n
a
m
e
s
p
a
c
e
=
b
.
o
i
d
W
H
E
R
E
p
r
o
n
a
m
e
=
′
x
x
x
′
存储过程
:
S
E
L
E
C
T
′
C
R
E
A
T
E
O
R
R
E
P
L
A
C
E
P
R
O
C
E
D
U
R
E
x
x
x
′
∣
∣
′
(
′
∣
∣
c
h
r
(
10
)
∣
∣
R
E
P
L
A
C
E
(
C
O
A
L
E
S
C
E
(
p
g
c
a
t
a
l
o
g
.
p
g
g
e
t
f
u
n
c
t
i
o
n
a
r
g
u
m
e
n
t
s
(
a
.
o
i
d
)
,
′
′
)
,
′
,
′
,
′
,
′
∣
∣
c
h
r
(
10
)
)
∣
∣
c
h
r
(
10
)
∣
∣
′
)
′
∣
∣
c
h
r
(
10
)
∣
∣
C
A
S
E
W
H
E
N
C
O
A
L
E
S
C
E
(
p
g
c
a
t
a
l
o
g
.
p
g
g
e
t
f
u
n
c
t
i
o
n
r
e
s
u
l
t
(
a
.
o
i
d
)
,
′
′
)
=
′
′
T
H
E
N
′
′
E
L
S
E
′
R
E
T
U
R
N
S
′
∣
∣
p
g
c
a
t
a
l
o
g
.
p
g
g
e
t
f
u
n
c
t
i
o
n
r
e
s
u
l
t
(
a
.
o
i
d
)
E
N
D
∣
∣
′
A
S
′
∣
∣
a
.
p
r
o
s
r
c
∣
∣
′
'|| chr(10)|| 'LANGUAGE plpgsql ' FROM pg_catalog.pg_proc a JOIN pg_catalog.pg_namespace b ON a.pronamespace = b.oid WHERE proname ='xxx' 存储过程: SELECT 'CREATE OR REPLACE PROCEDURE xxx '||'('||chr(10)|| REPLACE(COALESCE(pg_catalog.pg_get_function_arguments(a.oid),''),',',','||chr(10))||chr(10)||')' ||chr(10)||CASE WHEN COALESCE(pg_catalog.pg_get_function_result(a.oid),'')='' THEN '' ELSE 'RETURNS '||pg_catalog.pg_get_function_result(a.oid) END|| ' AS '|| a.prosrc ||'
′∣∣chr(10)∣∣′LANGUAGEplpgsql′FROMpgcatalog.pgprocaJOINpgcatalog.pgnamespacebONa.pronamespace=b.oidWHEREproname=′xxx′存储过程:SELECT′CREATEORREPLACEPROCEDURExxx′∣∣′(′∣∣chr(10)∣∣REPLACE(COALESCE(pgcatalog.pggetfunctionarguments(a.oid),′′),′,′,′,′∣∣chr(10))∣∣chr(10)∣∣′)′∣∣chr(10)∣∣CASEWHENCOALESCE(pgcatalog.pggetfunctionresult(a.oid),′′)=′′THEN′′ELSE′RETURNS′∣∣pgcatalog.pggetfunctionresult(a.oid)END∣∣′AS′∣∣a.prosrc∣∣′ '|| chr(10)|| 'LANGUAGE plpgsql ’
FROM pg_catalog.pg_proc a
JOIN pg_catalog.pg_namespace b ON a.pronamespace = b.oid
WHERE proname = ‘xxx’