Как найти функцию в postgresql

Yes, you cannot to find functions in pg_class because functions are stored on system table pg_proc

postgres-# df
                               List of functions
 Schema |        Name        | Result data type | Argument data types  |  Type  
--------+--------------------+------------------+----------------------+--------
 public | foo                | integer          | a integer, b integer | normal
 public | function_arguments | text             | oid                  | normal
(2 rows)

Query for list of custom functions based on pg_proc is simply

postgres=# select p.oid::regprocedure
              from pg_proc p 
                   join pg_namespace n 
                   on p.pronamespace = n.oid 
             where n.nspname not in ('pg_catalog', 'information_schema');
           oid           
-------------------------
 foo(integer,integer)
 function_arguments(oid)
(2 rows)

Most simply and fastest tests on functions existence are casting (without parameters) to regproc or regprocedure (with parameters):

postgres=# select 'foo'::regproc;
 regproc 
---------
 foo
(1 row)

postgres=# select 'foox'::regproc;
ERROR:  function "foox" does not exist
LINE 1: select 'foox'::regproc;
               ^
postgres=# select 'foo(int, int)'::regprocedure;
     regprocedure     
----------------------
 foo(integer,integer)
(1 row)

postgres=# select 'foo(int, text)'::regprocedure;
ERROR:  function "foo(int, text)" does not exist
LINE 1: select 'foo(int, text)'::regprocedure;
               ^

or you can do some similar with test against pg_proc

postgres=# select exists(select * from pg_proc where proname = 'foo');
 exists 
--------
 t
(1 row)

postgres=# select exists(select * 
                            from pg_proc 
                           where proname = 'foo' 
                             and function_arguments(oid) = 'integer, integer');
 exists 
--------
 t
(1 row)

where:

CREATE OR REPLACE FUNCTION public.function_arguments(oid)
RETURNS text LANGUAGE sql AS $function$
    select string_agg(par, ', ') 
       from (select format_type(unnest(proargtypes), null) par 
                from pg_proc where oid = $1) x
$function$

or you can use buildin functions:pg_get_function_arguments

p.s. trick for simply orientation in system catalog. Use a psql option -E:

[pavel@localhost ~]$ psql -E postgres
psql (9.2.8, server 9.5devel)
Type "help" for help.

postgres=# df
********* QUERY **********
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
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************

                               List of functions
 Schema |        Name        | Result data type | Argument data types  |  Type  
--------+--------------------+------------------+----------------------+--------
 public | foo                | integer          | a integer, b integer | normal
 public | function_arguments | text             | oid                  | normal
(2 rows)

Источник

There’s a handy function, oidvectortypes, that makes this a lot easier.

SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) 
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

Credit to Leo Hsu and Regina Obe at Postgres Online for pointing out oidvectortypes. I wrote similar functions before, but used complex nested expressions that this function gets rid of the need for.

See related answer.

(edit in 2016)

Summarizing typical report options:

-- Compact:
SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes))

-- With result data type: 
SELECT format(
       '%I.%I(%s)=%s', 
       ns.nspname, p.proname, oidvectortypes(p.proargtypes),
       pg_get_function_result(p.oid)
)

-- With complete argument description: 
SELECT format('%I.%I(%s)', ns.nspname, p.proname, pg_get_function_arguments(p.oid))

-- ... and mixing it.

-- All with the same FROM clause:
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

NOTICE: use p.proname||'_'||p.oid AS specific_name to obtain unique names, or to JOIN with information_schema tables — see routines and parameters at @RuddZwolinski’s answer.

The function’s OID (see pg_catalog.pg_proc) and the function’s specific_name (see information_schema.routines) are the main reference options to functions. Below, some useful functions in reporting and other contexts.

--- --- --- --- ---
--- Useful overloads: 

CREATE FUNCTION oidvectortypes(p_oid int) RETURNS text AS $$
    SELECT oidvectortypes(proargtypes) FROM pg_proc WHERE oid=$1;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION oidvectortypes(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in oidvectortypes(oid).
    SELECT oidvectortypes(proargtypes) 
    FROM pg_proc WHERE oid=regexp_replace($1, '^.+?([^_]+)$', '1')::int;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION pg_get_function_arguments(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in pg_get_function_arguments.
    SELECT pg_get_function_arguments(regexp_replace($1, '^.+?([^_]+)$', '1')::int)
$$ LANGUAGE SQL IMMUTABLE;

--- --- --- --- ---
--- User customization: 

CREATE FUNCTION pg_get_function_arguments2(p_specific_name text) RETURNS text AS $$
    -- Example of "special layout" version.
    SELECT trim(array_agg( op||'-'||dt )::text,'{}') 
    FROM (
        SELECT data_type::text as dt, ordinal_position as op
        FROM information_schema.parameters 
        WHERE specific_name = p_specific_name 
        ORDER BY ordinal_position
    ) t
$$ LANGUAGE SQL IMMUTABLE;

Источник

Here are three options for listing out all functions in a PostgreSQL database.

The information_schema.routines View

This view contains all functions and procedures in the current database that the current user has access to (by way of being the owner or having some privilege).

Here’s an example of returning a list of functions:

SELECT
    routine_name
FROM 
    information_schema.routines
WHERE 
    routine_type = 'FUNCTION'
AND
    routine_schema = 'public';

In this example, only functions with the public schema are returned. Feel free to include more columns if required.

The pg_proc Catalog

The pg_catalog.pg_proc catalog stores information about functions, procedures, aggregate functions, and window functions.

We can join this with the pg_catalog.pg_namespace catalog to filter the results to only procedures with the public namespace:

SELECT
    n.nspname,
    p.proname
FROM 
    pg_catalog.pg_namespace n
JOIN 
    pg_catalog.pg_proc p ON 
    p.pronamespace = n.oid
WHERE 
    p.prokind = 'f'
AND
    n.nspname = 'public';

We filtered to a prokind of f to limit the results to just normal functions.

The possible values are f for a normal function, p for a procedure, a for an aggregate function, or w for a window function.

The df Command

When using psql, we can use the df command:

df

By default, this returns only user-created objects. You can alternatively supply a pattern or the S modifier to include system objects.

Here’s an example of providing a pattern:

df *key*

That example narrows the results to just those functions/procedures with the text key in their name.

Note that this command also returns stored procedures. The routine type (e.g. func, proc) is listed in a type column in the output.

Источник

Всем привет!

Подскажите, пожалуйста, как через Dbeaver найти функцию, которую я написал?
Я же правильно понимаю, что когда я пишу create function , то она где-то создаётся?
Я воспользовался функцией pg_get_functiondef(имяфункции::regprocedure) и я вижу, что в результате вывода написано
public.имямоейфункции, т.е. я так понимаю, что она должна «осесть» в схеме public, но там её нет.

Может быть в Dbeaver есть какая-то возможность поиска функций по имени?

Заранее спасибо!

Источник

есть удобная функция, oidvectortypes, что делает это намного проще.

SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) 
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

кредит Leo Hsu и Regina Obe в Postgres Online указал oidvectortypes. Я писал подобные функции раньше, но использовал сложные вложенные выражения, которые эта функция избавляет от необходимости.

см. ответ.

(редактировать в 2016 году)

обобщение типичного отчета опции:

-- Compact:
SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes))

-- With result data type: 
SELECT format(
       '%I.%I(%s)=%s', 
       ns.nspname, p.proname, oidvectortypes(p.proargtypes),
       pg_get_function_result(p.oid)
)

-- With complete argument description: 
SELECT format('%I.%I(%s)', ns.nspname, p.proname, pg_get_function_arguments(p.oid))

-- ... and mixing it.

-- All with the same FROM clause:
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE ns.nspname = 'my_namespace';

обратите внимание используйте p.proname||'_'||p.oid AS specific_name чтобы получить уникальные имена, или присоединиться к information_schema таблицы — см. routines и parameters на ответ @ RuddZwolinski.

функции OID (см. pg_catalog.pg_proc) и функции specific_name (см. information_schema.routines) являются основными опциями для функций. Ниже приведены некоторые полезные функции в отчетности и других контекстах.

--- --- --- --- ---
--- Useful overloads: 

CREATE FUNCTION oidvectortypes(p_oid int) RETURNS text AS $$
    SELECT oidvectortypes(proargtypes) FROM pg_proc WHERE oid=;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION oidvectortypes(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in oidvectortypes(oid).
    SELECT oidvectortypes(proargtypes) 
    FROM pg_proc WHERE oid=regexp_replace(, '^.+?([^_]+)$', '')::int;
$$ LANGUAGE SQL IMMUTABLE;

CREATE FUNCTION pg_get_function_arguments(p_specific_name text) RETURNS text AS $$
    -- Extract OID from specific_name and use it in pg_get_function_arguments.
    SELECT pg_get_function_arguments(regexp_replace(, '^.+?([^_]+)$', '')::int)
$$ LANGUAGE SQL IMMUTABLE;

--- --- --- --- ---
--- User customization: 

CREATE FUNCTION pg_get_function_arguments2(p_specific_name text) RETURNS text AS $$
    -- Example of "special layout" version.
    SELECT trim(array_agg( op||'-'||dt )::text,'{}') 
    FROM (
        SELECT data_type::text as dt, ordinal_position as op
        FROM information_schema.parameters 
        WHERE specific_name = p_specific_name 
        ORDER BY ordinal_position
    ) t
$$ LANGUAGE SQL IMMUTABLE;
Источник

Понравилась статья? Поделить с друзьями:

Не пропустите также:

  • Как можно найти наследство
  • Как правильно найти простату у мужчины
  • Как найти дешевую квартиру для аренды
  • Как составить бизнес план ремонт обуви
  • Как составить схематический чертеж к задаче 4 класс математика

    • 0 0 голоса
    Рейтинг статьи
    Подписаться
    Уведомить о
    guest

    0 комментариев
    Старые
    Новые Популярные
    Межтекстовые Отзывы
    Посмотреть все комментарии