Pertanyaan Bagaimana saya bisa mendapatkan daftar semua fungsi yang tersimpan dalam database skema tertentu di PostgreSQL?


Saya ingin dapat terhubung ke database PostgreSQL dan menemukan semua fungsi untuk skema tertentu.

Pemikiran saya adalah bahwa saya bisa mengajukan pertanyaan ke pg_catalog atau information_schema dan mendapatkan daftar semua fungsi, tetapi saya tidak tahu di mana nama dan parameter disimpan. Saya mencari kueri yang akan memberi saya nama fungsi dan tipe parameter yang diperlukan (dan apa urutannya).

Apakah ada cara untuk melakukan ini?


75
2017-08-28 14:17


asal


Jawaban:


\df <schema>.*

di psql memberikan informasi yang diperlukan.

Untuk melihat permintaan yang digunakan secara internal terhubung ke database dengan psql dan memberikan tambahan "-E" (atau "--echo-hidden") opsi dan kemudian jalankan perintah di atas.


108
2017-08-28 16:27



Setelah beberapa pencarian, saya bisa menemukan information_schema.routines meja dan information_schema.parameters tabel. Dengan menggunakan itu, seseorang dapat membuat kueri untuk tujuan ini. LEFT JOIN, alih-alih JOIN, diperlukan untuk mengambil fungsi tanpa parameter.

SELECT routines.routine_name, parameters.data_type, parameters.ordinal_position
FROM information_schema.routines
    LEFT JOIN information_schema.parameters ON routines.specific_name=parameters.specific_name
WHERE routines.specific_schema='my_specified_schema_name'
ORDER BY routines.routine_name, parameters.ordinal_position;

55
2017-08-28 15:11



Jika ada yang tertarik di sini adalah permintaan apa yang dijalankan oleh psql pada postgres 9.1:

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;

Anda bisa mendapatkan apa psql dijalankan untuk perintah backslash dengan menjalankan psql dengan -E bendera.


22
2017-12-12 17:18



Ada fungsi yang berguna, oidvectortypes, yang membuat ini jauh lebih mudah.

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';

Kredit ke Leo Hsu dan Regina Obe di Postgres Online untuk menunjukkan oidvectortypes. Saya menulis fungsi serupa sebelumnya, tetapi menggunakan ekspresi bersarang yang kompleks sehingga fungsi ini menghilangkan kebutuhan.

Lihat jawaban terkait.


(edit di tahun 2016)

Meringkas opsi laporan umum:

-- 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';

MELIHAT: gunakan p.proname||'_'||p.oid AS specific_name  untuk mendapatkan nama yang unik, atau untuk bergabung dengan information_schema tabel - lihat routines dan parameters di jawaban @ RuddZwolinski.


Fungsi ini OID (Lihatpg_catalog.pg_proc) dan fungsi itu specific_name (Lihatinformation_schema.routines) adalah opsi referensi utama untuk fungsi. Di bawah ini, beberapa fungsi yang berguna dalam pelaporan dan konteks lainnya.

--- --- --- --- ---
--- 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;

18
2018-06-04 10:08



Jalankan di bawah kueri SQL untuk membuat tampilan yang akan menampilkan semua fungsi:

CREATE OR REPLACE VIEW show_functions AS
    SELECT routine_name FROM information_schema.routines 
        WHERE routine_type='FUNCTION' AND specific_schema='public';

11
2018-05-28 18:48



Adalah ide yang bagus bernama fungsi dengan alias komunik pada kata-kata pertama untuk filtre nama dengan LIKE Contoh dengan skema publik di Postgresql 9.4, pastikan untuk mengganti dengan skemanya

SELECT routine_name FROM information_schema.routines WHERE routine_type='FUNCTION' AND specific_schema='public' AND routine_name LIKE 'aliasmyfunctions%';

6
2017-12-23 10:03



Contoh:

perfdb-# \df information_schema.*;

List of functions
        Schema      |        Name        | Result data type | Argument data types |  Type  
 information_schema | _pg_char_max_length   | integer | typid oid, typmod integer | normal
 information_schema | _pg_char_octet_length | integer | typid oid, typmod integer | normal
 information_schema | _pg_datetime_precision| integer | typid oid, typmod integer | normal
 .....
 information_schema | _pg_numeric_scale     | integer | typid oid, typmod integer | normal
 information_schema | _pg_truetypid         | oid     | pg_attribute, pg_type     | normal
 information_schema | _pg_truetypmod        | integer | pg_attribute, pg_type     | normal
(11 rows)

3
2017-09-24 11:58