psql: display functions with \df that do *not* contain a certain pattern

Question

Because I installed dblink extension into the (default) public schema, \df will display all the dblink related functions. I only want to view customized function.

How can I see function that not contain text "dblink"?
So far what I tried.

\df !~ *dblink*
\df !~ *dblink*
\df ! *dblink*
\df !*dblink*
\df ! *'dblink'
\df !~* 'dblink'
\df !~ 'dblink'
\df !~ dblink

\df dblink* will get all the function begin with "dblink". I just want the opposite.

regex link:
https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-POSIX-REGEXP psql link:
https://www.postgresql.org/docs/current/app-psql.html#APP-PSQL-PATTERNS

Adsorbent

That's one of the reasons I install extensions into a separate schema. – Gona

You cannot get that with \df, you'll have to write a metadata query. – Suspect

@LaurenzAlbe Advanced users can use regular-expression notations such as character classes, for example [0-9] to match any digit. All regular expression special characters work as specified in Section 9.7.3, except for . which is taken as a separator as mentioned above, * which is translated to the regular-expression notation .*, ? which is translated to ., and $ which is matched literally. Seems there is no specific mention of we cannot do that. – Adsorbent

not is an operators. I guess this is the issue. – Adsorbent

Your problem is that ~, !~ etc. are operators. You won't easily find a regular expression that matches everything that does not contain a certain string. – Suspect

@LaurenzAlbe: Not that easily, but I think I found an bearable workaround. – Playhouse

Answer 1

What you asked

How can I see function that not contain text "dblink",

Regular expressions are supported in these patterns. Without adding an operator like !~*.
Unfortunately, some special characters are excluded. A regexp with a negative lookahead would solve your request nicely. But no luck, because that happens to involve the ? characters and, quoting the manual:

All regular expression special characters work as specified in Section 9.7.3, except for [...], ? which is translated to . [...]

Barring that, I can only think of character classes to exclude patterns as a workaround:

test=> \df public.([^d]|.[^b]|..[^l]|...[^i]|....[^n]|.....[^k])*

Displays all tables in the schema public that do not start with 'dblink'. It's a regular expression with branches:

• The 1st branch [^d] allows all names that do not start with 'd',
• The 2nd branch .[^b] allows all without 'b' as 2nd character

Etc.

Since the long string is awkward to type, you can save it to a psql variable, and interpolate that:

test=> \set dbx 'public.([^d]|.[^b]|..[^l]|...[^i]|....[^n]|.....[^k])*'
test=> \df :dbx

You can even put that \set command into your ~/.psqlrc file to have it loaded in psql automatically.

SQL solution

Alternatively, you can query the system catalogs:

SELECT n.nspname AS "Schema", p.proname AS "Name"
     , pg_catalog.pg_get_function_result(p.oid) AS "Result data type"
     , pg_catalog.pg_get_function_arguments(p.oid) AS "Argument data types"
     , CASE p.prokind WHEN 'a' THEN 'agg' WHEN 'w' THEN 'window' WHEN 'p' THEN 'proc' ELSE 'func' END AS "Type"
FROM   pg_catalog.pg_proc p
LEFT   JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE  pg_function_is_visible (p.oid)  -- only visible functions like plain \df
AND    n.nspname NOT LIKE 'pg\_%'      -- exclude catalog schemas
AND    p.proname NOT LIKE 'dblink%'    -- exclude pattern
ORDER  BY 1, 2, 4;

While this is a lot more versatile, it is also more complicated. And may change for future versions ...

Simpler: separate schema for extensions

That said, the clean solution is to avoid the mess and install extensions into a separate schema, like a_horse already hinted. This related answer has detailed instructions:

• Is it recommended to install extensions into pg_catalog schema?

If you go that route, and use a schema named extensions, you can then simply change the schema where dblink is installed with:

ALTER EXTENSION dblink SET SCHEMA extensions;

Playhouse