Blog Post by Murat Yaşar

1. Blog
2. Show All Functions and Views In PostgreSQL By Using Select Statement And Querying pgCatalog
3. Technical

---

Posted on Saturday, February 18, 2012 5:29:31 PM and it has been read 2483 times since then.

---

Show All Functions and Views In PostgreSQL By Using Select Statement And Querying pgCatalog

I was looking for a solution in order to see all functions in postgres database.

After searching on the internet and summing up my needs, I came up with a solution and it was kind of generic.

I used the following select statements inside a function and I called this function from a windows service and got the result and created a txt file for each returned row from the query.

By doing this I intended to backed up my function's and view's scripts in case I needed older version of them.

As you can understand from the parameters, you are able to specify schema name and server information and the function serves the data that you need.

If you need more information or sample code please contact me via contact me page or sign up and comment on this article. I will reply to you as soon as possible.

---

The following script gets all the function for specified schema name.

SELECT FunctionBackups.* FROM dblink('dbname=mydb port=' || prmport:: character varying || 
' host=' || prmserverip:: character varying || ' user=' || prmusername:: character varying || 
' password=' || prmpassword:: character varying || '',
'SELECT nspname::character varying as "SchemaName", 
proname::character varying AS "FunctionName", 
prosrc::character varying AS "FunctionContent", 
pg_proc::character varying AS "FunctionContentWithParameters"
FROM pg_catalog.pg_namespace
JOIN pg_catalog.pg_proc
ON pronamespace = pg_namespace.oid
WHERE nspname =''' || 
prmschemaname::character varying || '''') AS FunctionBackups(nspname character varying,
proname character varying,prosrc character varying,pg_proc character varying);

---

The following script gets all the view for specified schema name.

SELECT ViewBackups.* FROM dblink('dbname=mydb port=' || prmport:: character varying || 
' host=' || prmserverip:: character varying || ' user=' || prmusername:: character varying || 
' password=' || prmpassword:: character varying || '',
'SELECT schemaname::character varying as "SchemaName", 
viewname::character varying AS "ViewName", 
viewowner::character varying AS "ViewOwner", 
definition::character varying AS "Definition"
FROM pg_views
WHERE schemaname =''' || prmschemaname::character varying || '''') AS ViewBackups(schemaname character varying,
viewname character varying,viewowner character varying,definition character varying);

Have a great day.

---

---