Search code examples
regexpostgresqlpsqlpostgresql-extensions

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

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

Solution

  • 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:

    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;