CODEWITHSUNDEEP
sqlpostgresqlfunctionpg-dump
vaab
vaab

Reputation: 10122

Use SQL to get the same list of function that are part of a `pg_dump`

Using pg_dump, we get a dump that contain function definitions (amongst many other data). Here is a simple bash script that output the names of the functions:

pg_dump --no-owner mydatabase | grep ^"CREATE FUNCTION" | cut -f 3 -d " " | cut -f 1 -d "("

How can I get the equivalent list with pure postgres SQL ?. I have tried many other answers here on stackoverflow to list functions in postgres, and I usually get way more functions.

EDIT:

If this is important, I'm on postgres 10.14. This is a postgis-ready database, the mydatabase is created empty and then a few extension are installed (amongst other, postgis) and then it is initialized by replaying a dump containing (amongst regular table schema and data) a list of CREATE FUNCTION .... I suspect the application to add probably a few others after running, and the list of function I get in pg_dump is close to those coming from the initial dump replayed.

The bash script doesn't output postgis function names.

If that helps:

$ echo "\df" | psql -qAt mydatabase | wc -l
756
$ pg_dump --no-owner mydatabase | grep ^"CREATE FUNCTION" | cut -f 3 -d " " | cut -f 1 -d "(" | wc -l
30

EDIT2:

Main issue seems to be in 2 parts:

Upvotes: 0

Views: 238

Answers (1)

AdamKG
AdamKG

Reputation: 14091

Here is the relevant code:

https://github.com/postgres/postgres/blob/65aaed22a849c0763f38f81338a1cad04ffc0e2c/src/bin/pg_dump/pg_dump.c#L6087

See also the detailed explanatory comment ~30 lines earlier. I copy/pasted the string into a python shell to concatenate the lines, resulting in:

SELECT p.tableoid, p.oid, p.proname, p.prolang, p.pronargs, p.proargtypes, p.prorettype, p.proacl, acldefault('f', p.proowner) AS acldefault, p.pronamespace, (%s p.proowner) AS rolname FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid = pip.objoid AND pip.classoid = 'pg_proc'::regclass AND pip.objsubid = 0) WHERE %s
  AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_proc'::regclass AND objid = p.oid AND deptype = 'i')
  AND (
  pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog')
  OR EXISTS (SELECT 1 FROM pg_cast
  WHERE pg_cast.oid > %u 
  AND p.oid = pg_cast.castfunc)
  OR EXISTS (SELECT 1 FROM pg_transform
  WHERE pg_transform.oid > %u AND 
  (p.oid = pg_transform.trffromsql
  OR p.oid = pg_transform.trftosql)

From looking at the code, we can see that the first %s should be username_subquery, the second should be not_agg_check, and the last two are g_last_builtin_oid. I did have to dig around around the file a bit to find those values (and, for g_last_builtin_oid, a google search for the FirstNormalObjectId that it's defined in terms of). The final result, leaving only the human-relevant columns, for PG 10 (differs a bit for newer versions):

testdb=# SELECT p.proname, p.pronamespace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =p.proowner) AS rolname FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid = pip.objoid AND pip.classoid = 'pg_proc'::regclass AND pip.objsubid = 0) WHERE NOT p.proisagg
  AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_proc'::regclass AND objid = p.oid AND deptype = 'i')
  AND (
  pronamespace != (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog')
  OR EXISTS (SELECT 1 FROM pg_cast
  WHERE pg_cast.oid > 16384
  AND p.oid = pg_cast.castfunc)
  OR EXISTS (SELECT 1 FROM pg_transform
  WHERE pg_transform.oid > 16384 AND 
  (p.oid = pg_transform.trffromsql
  OR p.oid = pg_transform.trftosql)));
           proname           | pronamespace | rolname  
-----------------------------+--------------+----------
 blhandler                   |         2200 | myrole
 bar                         |         2200 | myrole
 foo                         |         2200 | myrole
 _pg_expandarray             |        13327 | postgres
 _pg_keysequal               |        13327 | postgres
 _pg_index_position          |        13327 | postgres
 _pg_truetypid               |        13327 | postgres
 _pg_truetypmod              |        13327 | postgres
 _pg_char_max_length         |        13327 | postgres
 _pg_char_octet_length       |        13327 | postgres
 _pg_numeric_precision       |        13327 | postgres
 _pg_numeric_precision_radix |        13327 | postgres
 _pg_numeric_scale           |        13327 | postgres
 _pg_datetime_precision      |        13327 | postgres
 _pg_interval_type           |        13327 | postgres
(15 rows)

(this is in a test db I had lying around, not sure what those _pg_* funcs are, but the namespace is information_schema so whatever)

Upvotes: 1

Related Questions