Re: Scripting function definitions as SQL?

On Sun, 2008-05-11 at 06:12 -0700, Postgres User wrote:
> Has anyone written a function that scripts out all the functions in a
> database as full SQL statements (Create Function.....)

You could pg_dump the schema in the "custom" format (-Fc), then call
pg_restore with -l to get the TOC, grep the TOC for functions, and feed
that back into pg_restore with -L. It sounds like a lot, but it's pretty
easy in practice, like so:

$ sudo -u postgres pg_dump -Fc -s mydb >mydb.pgdfc
$ pg_restore -l mydb.pgdfc >mydb.toc
$ grep -E '^[0-9]+; [0-9]+ [0-9]+ FUNCTION' mydb.toc >mydb-fx.toc
$ pg_restore -L mydb-fx.toc mydb.pgdfc 

The output of pg_restore is sql.

This technique is extremely useful for other kinds of schema elements as
well.

-Reece

-- 
Reece Hart, http://harts.net/reece/,
GPG:0x25EC91A0


-- 
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general