How to get the list of functions,or view populating a particular table in pgsql?

Question

I am trying to figure out the source system which are populating a particular table in pg admin.

Can i do it with information_schema or pg_proc I tried but i don't know exact query. can anyone help me?

pg_proc will give us the source of a procedure. so i tried information_schema.tables but in this no source values are stored.plz help me with the exact query — infa, Jan 30 '15 at 07:12
maybe you are not a owner - information schema shows only your tables. — Pavel Stehule, Jan 30 '15 at 07:56
Information schema vs. system catalogs: http://stackoverflow.com/questions/20582500/how-to-check-if-a-table-exists-in-a-given-schema/24089729#24089729 — Erwin Brandstetter, Jan 31 '15 at 12:10

score 0 · Answer 1 · answered Jan 30 '15 at 07:59

There is a nice trick, how to get a complete queries to system catalog.

Run psql with option -E. It means echo all. Then psql shows all statements generated by backslash psql commands (like \df for list of functions):

[pavel@localhost plpgsql_check]$ psql -E postgres
psql (9.5devel)
Type "help" for help.

postgres=# \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname  'pg_catalog'
      AND n.nspname  'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

So you have to know a backslash statements - some basic list:

\dt - list of tables
\df - list of functions
\dn - list of schemas
\l - list of databases
\? - list of backslash statements

How to get the list of functions,or view populating a particular table in pgsql?

1 Answers1