PostreSQL: export column2 to be the contents of a new file, where the filename is column2

Question

I need to pull out some data from a PostgreSQL database - namely some old "lost" functions.

I've got a select statement which pulls up the function name as column1, and the function definition as column2.

Is there any easy (or, for that matter, difficult) way to automagically create a filename with the name column1, and dump the contents of column2 to that file?

Ideally this would be on a one-file-per-row export, although I suppose if I can output it to a single file I can create a quick script to split it to individual files

Any pointers appreciated!

Thanks

Edit:

I've tried the following

SELECT pg_get_functiondef(f.oid)) INTO OUTFILE proname
FROM pg_catalog.pg_proc f
INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid)
WHERE n.nspname = 'my_namespace'

The select works fine without the INTO OUTFILE... clause, but I get a syntax error with that clause. This is whether I use the proname, or a '/tmp/test.sql' type file path.

You can try to create a function in `PL/Py` language (or any other language, that can easily write files) and inside the function loop through `SELECT proname, prosrc FROM pg_proc` and write files. — Ihor Romanchenko, Nov 18 '13 at 15:16
Or disable formatting in `psql` and output `SELECT proname, prosrc FROM pg_proc` into a file. — Ihor Romanchenko, Nov 18 '13 at 15:19
I've got the following SELECT proname, (pg_get_functiondef(f.oid)) FROM pg_catalog.pg_proc f INNER JOIN pg_catalog.pg_namespace n ON (f.pronamespace = n.oid) WHERE n.nspname = ''; But this leaves me having to concatenate the fields together into one file, then separate them out again. I suspect your first suggestion will be more useful, as if I select the whole thing into a file I'll just have to write another script to separate it again anyway! Thanks — Jon Story, Nov 18 '13 at 15:55

score 0 · Answer 1 · answered Dec 10 '13 at 05:00

In general, untrusted queries cannot create files. So you would need to have a function created by a superuser that would do this. This could be done with a security definer plpgsql function and dynamic sql, or with a function in an untrusted language like pl/python or pl/perlU.

However, you need to be very careful with this. Suppose you store your files in ~/spool (/var/lib/pgsql/spool or the like depending on your system). Suppose I:

 CREATE TABLE poof (
      "../data/base/1/1001" int
 );

If you aren't careful, with a few tries I could corrupt your whole db instance! So you would also need to check your column names for sane inputs.

PostreSQL: export column2 to be the contents of a new file, where the filename is column2

1 Answers1