System table with function's definitions in Postgresql

Question

I would like to make correction in triggers, but there are like 200-300 cases of this issue - far too much to do it manually. Is there any table with whole db code in Postgresql where I could just find all their definitions and correct them all at the same time?

That's probably beginner question (I know there's table like that in Oracle, but I'm new to Postgresql) but I can't seem to find it neither in web nor on my own.

Easiest is to use pg_dump -U the_user --schema-only the_dbname >>textfile.sql, and use the contents of the textfile. The textfile will contain all the DDL, including tables, indexes, views, functions, but these are easy to delete from the textfile. — wildplasser
– wildplasser, Commented Dec 3, 2014 at 14:46
Seems helpful. What directory does the file appear in? Can't seem to find it with 'find -name 'textfile*' but I'm not really effective when working on linux — Hisu
– Hisu, Commented Dec 3, 2014 at 14:59
There is no file. output is sent to stdout. (in my example, I redirected it to a file with >>textfile ) There is also an --file=FILENAME option/flag to the pg_dump command — wildplasser
– wildplasser, Commented Dec 3, 2014 at 15:02
I've used -f (as I understand it works the same way as --file) but nothing really happens. I mean whole thing doesn't even last 1-2 sec which seems impossible to me. As a filename do I put only the name, or maybe whole directory? and will it create the file,, or do I have to create empty file with that name beforehand? And last thing - do I put all this in psql? Or maybe somehow in postgresql editor? — Hisu
– Hisu, Commented Dec 3, 2014 at 15:16
pg_dump is a command (program); you run it by typing its name (plus the arguments) on the (linux) command line. And yes: it is fast, 1..2 sec is certainly possible. [linux] if a filename does not contain a directory path, the current directory is assumed. — wildplasser
– wildplasser, Commented Dec 3, 2014 at 15:22

Robert M. Lefkowitz · Accepted Answer · 2014-12-03 16:19:35Z

1

The table that contains the definitions for all procedures in postgres is pg_catalog.pg_proc.

The column with the source code is prosrc.

You can do something like:

select proname, prosrc from pg_proc where proname like '%doc%';

assuming you have some functions named doc.

The stored procedure pg_catalog.pg_get_functiondef will return the source code of a particular function. You can do something like:

select pg_get_functiondef(oid) from pg_proc where proname='myfunction_name';

Unfortunately, pg_get_functiondef uses an aggregate function internally -- so you cannot call pg_get_functiondef in a query that is returning more than one function.

answered Dec 3, 2014 at 16:19

Robert M. Lefkowitz

1,52511 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hisu Over a year ago

Already used the work-around with pg_dump, but pg_proc table is exactly what I was looking for, and it certainly will be useful in another case like that. Thanks for help.

Collectives™ on Stack Overflow

System table with function's definitions in Postgresql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related