Blog Post by Murat Yaşar


Posted on Saturday, February 18, 2012 5:29:31 PM and it has been read 1214 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.


(In order to use this feature, you have to register.)

Tag Related Blog Entries

Compare Tables Row By Row in PostgreSQL

Sunday, June 21, 2015 2   6054  

Some Helpful Links For Software Developers

Saturday, April 28, 2012 0   4012  

Npgsql Data Types And Equivalents

Monday, March 21, 2011 2   3541  

DataBinding GridView In Asp.NET Application By Using PostgreSQL as DataSource

Sunday, October 10, 2010 0   5178  

PostgreSQL - PL/pgSQL Little Code Snippets

Friday, October 08, 2010 3   1998  

PostgreSQL 8.4.4 Data Types

Monday, September 27, 2010 0   1132