How to save multiple functions as each files(*.sql) in Postgresql?

Question

I'm using PostgreSQL version 8.2 and trying to save all functions/views source code as each files on a local. But it seems to be impossible to extract each function's code to each files.

example:

I'd like to save source files like below:

function a1, a2, a3 -----> a1.sql, a2.sql, a3.sql

How can I do it?

Answer 1

This is not a complete solution, but should get you started in the right direction.

---

Assuming you are using psql for this, you can save the source code of a single function by spooling the output of select procsrc from pg_proc where proname = '...' to a file, e.g.:

\t
\a
\o a1.sql
select prosrc from pg_proc where proname = 'a1';
\o

The \t \a configures psql to only output the content of the column prosrc. The \o a1.sql redirects the query result to a file.

As you want one file per function, you need to run this for each function. You can either do this manually or generate a script which does this by running a SQL statement:

\t \a \o extract_source.sql
select concat('\t '\a \o ', proname, '.sql', chr(10), 
              'select prosrc from pg_proc where proname = ''', proname, ''';', 
              chr(10), 
              '\o', chr(10))
from pg_proc;
\o 
\i extract_source.sql

The query generates the first script for each procedure, then runs the generated script.

Not tested, you probably need to tweak that to meet your needs.

---

Note that the content of prosrc is NOT the complete function.

It lacks the return type, the arguments and the language definition. In more modern Postgres versions you could use pg_get_functiondef() for that, but with 8.2 you will need to put all those things together using the columns from pg_proc. The most complicated part is getting the function arguments right (because Postgres 8.2 also lacks, pg_get_function_arguments())

Without the arguments, it will be something like:

select concat('create or replace function ', 
              proname, '(......) ', 
              'returns ', rt.typname, 
              ' as $$ ', 
              prosrc, 
              '$$ language ', l.lanname, ';')
from pg_proc p
  join pg_language l on l.oid = p.prolang
  join pg_type rt on rt.oid = p.prorettype
where proname = 'a1';

instead of "just" select prosrc .... from pg_proc.