Skip to content

Commit 07055a2

Browse files
peterepetere
committed
psql: Add option for procedures to \df
1 parent 9915735 commit 07055a2

File tree

6 files changed

+76
-20
lines changed

6 files changed

+76
-20
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1423,16 +1423,16 @@ testdb=>
14231423

14241424

14251425
<varlistentry>
1426-
<term><literal>\df[antwS+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
1426+
<term><literal>\df[anptwS+] [ <link linkend="app-psql-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
14271427

14281428
<listitem>
14291429
<para>
14301430
Lists functions, together with their result data types, argument data
14311431
types, and function types, which are classified as <quote>agg</quote>
1432-
(aggregate), <quote>normal</quote>, <quote>trigger</quote>, or <quote>window</quote>.
1432+
(aggregate), <quote>normal</quote>, <quote>procedure</quote>, <quote>trigger</quote>, or <quote>window</quote>.
14331433
To display only functions
14341434
of specific type(s), add the corresponding letters <literal>a</literal>,
1435-
<literal>n</literal>, <literal>t</literal>, or <literal>w</literal> to the command.
1435+
<literal>n</literal>, <literal>p</literal>, <literal>t</literal>, or <literal>w</literal> to the command.
14361436
If <replaceable
14371437
class="parameter">pattern</replaceable> is specified, only
14381438
functions whose names match the pattern are shown.

‎src/bin/psql/command.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -754,6 +754,7 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
754754
case 'S':
755755
case 'a':
756756
case 'n':
757+
case 'p':
757758
case 't':
758759
case 'w':
759760
success = describeFunctions(&cmd[2], pattern, show_verbose, show_system);

‎src/bin/psql/describe.c

Lines changed: 38 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -312,6 +312,7 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
312312
{
313313
bool showAggregate = strchr(functypes, 'a') != NULL;
314314
bool showNormal = strchr(functypes, 'n') != NULL;
315+
bool showProcedure = strchr(functypes, 'p') != NULL;
315316
bool showTrigger = strchr(functypes, 't') != NULL;
316317
bool showWindow = strchr(functypes, 'w') != NULL;
317318
bool have_where;
@@ -323,25 +324,39 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
323324
/* No "Parallel" column before 9.6 */
324325
static const bool translate_columns_pre_96[] = {false, false, false, false, true, true, false, true, false, false, false, false};
325326

326-
if (strlen(functypes) != strspn(functypes, "antwS+"))
327+
if (strlen(functypes) != strspn(functypes, "anptwS+"))
327328
{
328-
psql_error("\\df only takes [antwS+] as options\n");
329+
psql_error("\\df only takes [anptwS+] as options\n");
330+
return true;
331+
}
332+
333+
if (showProcedure && pset.sversion < 110000)
334+
{
335+
char sverbuf[32];
336+
337+
psql_error("\\df does not take a \"%c\" option with server version %s\n",
338+
'p',
339+
formatPGVersionNumber(pset.sversion, false,
340+
sverbuf, sizeof(sverbuf)));
329341
return true;
330342
}
331343

332344
if (showWindow && pset.sversion < 80400)
333345
{
334346
char sverbuf[32];
335347

336-
psql_error("\\df does not take a \"w\" option with server version %s\n",
348+
psql_error("\\df does not take a \"%c\" option with server version %s\n",
349+
'w',
337350
formatPGVersionNumber(pset.sversion, false,
338351
sverbuf, sizeof(sverbuf)));
339352
return true;
340353
}
341354

342-
if (!showAggregate && !showNormal && !showTrigger && !showWindow)
355+
if (!showAggregate && !showNormal && !showProcedure && !showTrigger && !showWindow)
343356
{
344357
showAggregate = showNormal = showTrigger = true;
358+
if (pset.sversion >= 110000)
359+
showProcedure = true;
345360
if (pset.sversion >= 80400)
346361
showWindow = true;
347362
}
@@ -505,7 +520,7 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
505520
have_where = false;
506521

507522
/* filter by function type, if requested */
508-
if (showNormal && showAggregate && showTrigger && showWindow)
523+
if (showNormal && showAggregate && showProcedure && showTrigger && showWindow)
509524
/* Do nothing */ ;
510525
else if (showNormal)
511526
{
@@ -523,6 +538,17 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
523538
else
524539
appendPQExpBufferStr(&buf, "NOT p.proisagg\n");
525540
}
541+
if (!showProcedure && pset.sversion >= 110000)
542+
{
543+
if (have_where)
544+
appendPQExpBufferStr(&buf, " AND ");
545+
else
546+
{
547+
appendPQExpBufferStr(&buf, "WHERE ");
548+
have_where = true;
549+
}
550+
appendPQExpBufferStr(&buf, "p.prokind <> 'p'\n");
551+
}
526552
if (!showTrigger)
527553
{
528554
if (have_where)
@@ -572,6 +598,13 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool
572598
"p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype\n");
573599
needs_or = true;
574600
}
601+
if (showProcedure)
602+
{
603+
if (needs_or)
604+
appendPQExpBufferStr(&buf, " OR ");
605+
appendPQExpBufferStr(&buf, "p.prokind = 'p'\n");
606+
needs_or = true;
607+
}
575608
if (showWindow)
576609
{
577610
if (needs_or)

‎src/bin/psql/help.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -235,7 +235,7 @@ slashUsage(unsigned short int pager)
235235
fprintf(output, _(" \\des[+] [PATTERN] list foreign servers\n"));
236236
fprintf(output, _(" \\deu[+] [PATTERN] list user mappings\n"));
237237
fprintf(output, _(" \\dew[+] [PATTERN] list foreign-data wrappers\n"));
238-
fprintf(output, _(" \\df[antw][S+] [PATRN] list [only agg/normal/trigger/window] functions\n"));
238+
fprintf(output, _(" \\df[anptw][S+] [PATRN] list [only agg/normal/procedures/trigger/window] functions\n"));
239239
fprintf(output, _(" \\dF[+] [PATTERN] list text search configurations\n"));
240240
fprintf(output, _(" \\dFd[+] [PATTERN] list text search dictionaries\n"));
241241
fprintf(output, _(" \\dFp[+] [PATTERN] list text search parsers\n"));

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

Lines changed: 24 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -15,14 +15,6 @@ LANGUAGE SQL
1515
AS $$
1616
INSERT INTO cp_test VALUES (1, x);
1717
$$;
18-
SELECT ptest1('x'); -- error
19-
ERROR: ptest1(unknown) is a procedure
20-
LINE 1: SELECT ptest1('x');
21-
^
22-
HINT: To call a procedure, use CALL.
23-
CALL ptest1('a'); -- ok
24-
CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg
25-
CALL ptest1(substring(random()::numeric(20,15)::text, 1, 1)); -- ok, volatile arg
2618
\df ptest1
2719
List of functions
2820
Schema | Name | Result data type | Argument data types | Type
@@ -41,6 +33,30 @@ SELECT pg_get_functiondef('ptest1'::regproc);
4133

4234
(1 row)
4335

36+
-- show only normal functions
37+
\dfn public.*test*1
38+
List of functions
39+
Schema | Name | Result data type | Argument data types | Type
40+
--------+--------------+------------------+---------------------+------
41+
public | cp_testfunc1 | integer | a integer | func
42+
(1 row)
43+
44+
-- show only procedures
45+
\dfp public.*test*1
46+
List of functions
47+
Schema | Name | Result data type | Argument data types | Type
48+
--------+--------+------------------+---------------------+------
49+
public | ptest1 | | x text | proc
50+
(1 row)
51+
52+
SELECT ptest1('x'); -- error
53+
ERROR: ptest1(unknown) is a procedure
54+
LINE 1: SELECT ptest1('x');
55+
^
56+
HINT: To call a procedure, use CALL.
57+
CALL ptest1('a'); -- ok
58+
CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg
59+
CALL ptest1(substring(random()::numeric(20,15)::text, 1, 1)); -- ok, volatile arg
4460
SELECT * FROM cp_test ORDER BY b COLLATE "C";
4561
a | b
4662
---+-------

‎src/test/regress/sql/create_procedure.sql

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -11,14 +11,20 @@ AS $$
1111
INSERT INTO cp_test VALUES (1, x);
1212
$$;
1313

14+
\df ptest1
15+
SELECT pg_get_functiondef('ptest1'::regproc);
16+
17+
-- show only normal functions
18+
\dfn public.*test*1
19+
20+
-- show only procedures
21+
\dfp public.*test*1
22+
1423
SELECT ptest1('x'); -- error
1524
CALL ptest1('a'); -- ok
1625
CALL ptest1('xy' || 'zzy'); -- ok, constant-folded arg
1726
CALL ptest1(substring(random()::numeric(20,15)::text, 1, 1)); -- ok, volatile arg
1827

19-
\df ptest1
20-
SELECT pg_get_functiondef('ptest1'::regproc);
21-
2228
SELECT * FROM cp_test ORDER BY b COLLATE "C";
2329

2430

0 commit comments

Comments
 (0)