How can script from pg_get_functiondef() be used to create stored procedures in Postgresql?

Question

Using the following code, the text file "myprocedures" containing the stored procedures is created.

Is there anyway without alteration to apply this file to an existing database on another machine? Or how best to use this file to generate the procedures on another database? (i.e., how can it be used directly in PgAdmin or psql?).

My problem is there are symbols (like "+" and "\r" generated in the script that are not recognized with

"C:\Program Files\PostgreSQL\9.3\bin\psql.exe" -h xxx.xxx.x.xxx -p 5432 -U postgres -d chaos -1 -v ON_ERROR_STOP -f C:/temp/myprocedures

TIA

PostgreSQL version 9.3

Windows 7 Ultimate

PgAdmin III

File: dump_stored_procedures.sql

SELECT 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 = 'public';

--------------------------------------------------------

File: dump_procedures.bat

setlocal
set PGPASSWORD=password
"C:\Program Files\PostgreSQL\9.3\bin\psql.exe" -h localhost -U postgres 
-d  chaos -f C:/temp/dump_stored_procedures.sql >> C:/temp/myprocedures
pause
endlocal

Edit #1: Here is an example of the first few procedure definitions in myprocedures. Note the unnecessary addition of "pg_get_function", the "---", and all the "+" and "\r":

    pg_get_functiondef                                                                                                                                                                                
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.empty(text)                                                                                                                                                                                                                                                                                                                                  +
  RETURNS boolean                                                                                                                                                                                                                                                                                                                                                               +
  LANGUAGE sql                                                                                                                                                                                                                                                                                                                                                                  +
  IMMUTABLE                                                                                                                                                                                                                                                                                                                                                                     +
 AS $function$ SELECT $1 ~ '^[[:space:]]*$'; $function$                                                                                                                                                                                                                                                                                                                         +

 CREATE OR REPLACE FUNCTION public.f_getallprogressnotes(groupid character varying)                                                                                                                                                                                                                                                                                             +
  RETURNS SETOF view_progressnote                                                                                                                                                                                                                                                                                                                                               +
  LANGUAGE sql                                                                                                                                                                                                                                                                                                                                                                  +
 AS $function$ \r                                                                                                                                                                                                                                                                                                                                                               +
 \r                                                                                                                                                                                                                                                                                                                                                                             +
  select \r                                                                                                                                                                                                                                                                                                                                                                     +
         et.eid, et.groupid, et.cpatient, et.tencounter, et.checkout, et.notseen,  \r                                                                                                                                                                                                                                                                                           +
         et.complexity,  p.note, r.appointmentmetadata, r.rtn\r                                                                                                                                                                                                                                                                                                                 +
  from \r                                                                                                                                                                                                                                                                                                                                                                       +
         encountertimes et \r                                                                                                                                                                                                                                                                                                                                                   +
  left outer join \r                                                                                                                                                                                                                                                                                                                                                            +
         progressnote p \r                                                                                                                                                                                                                                                                                                                                                      +
  on \r                                                                                                                                                                                                                                                                                                                                                                         +
         (et.eid = p.eid)\r                                                                                                                                                                                                                                                                                                                                                     +
  left outer join \r                                                                                                                                                                                                                                                                                                                                                            +
         returntooffice r \r                                                                                                                                                                                                                                                                                                                                                    +
  on \r                                                                                                                                                                                                                                                                                                                                                                         +
         (et.eid = r.eid) \r                                                                                                                                                                                                                                                                                                                                                    +
  where\r                                                                                                                                                                                                                                                                                                                                                                       +
         et.groupid =$1 \r                                                                                                                                                                                                                                                                                                                                                      +
 order by et.tencounter desc     \r                                                                                                                                                                                                                                                                                                                                             +
 \r                                                                                                                                                                                                                                                                                                                                                                             +
 $function$                                                                                                                                                                                                                                                                                                                                                                     +

 CREATE OR REPLACE FUNCTION public.f_getallvitalsigns(groupid character varying)                                                                                                                                                                                                                                                                                                +
  RETURNS SETOF vitalsigns                                                                                                                                                                                                                                                                                                                                                      +
  LANGUAGE sql                                                                                                                                                                                                                                                                                                                                                                  +
 AS $function$ \r                                                                                                                                                                                                                                                                                                                                                               +
 select v.*\r                                                                                                                                                                                                                                                                                                                                                                   +
   from \r                                                                                                                                                                                                                                                                                                                                                                      +
         vitalsigns v \r                                                                                                                                                                                                                                                                                                                                                        +
   right join ( \r                                                                                                                                                                                                                                                                                                                                                              +
         select \r                                                                                                                                                                                                                                                                                                                                                              +
                 eid, tencounter \r                                                                                                                                                                                                                                                                                                                                             +
         from \r                                                                                                                                                                                                                                                                                                                                                                +
                 encountertimes\r                                                                                                                                                                                                                                                                                                                                               +
         where\r                                                                                                                                                                                                                                                                                                                                                                +
                 groupid = $1\r                                                                                                                                                                                                                                                                                                                                                 +
         order by\r                                                                                                                                                                                                                                                                                                                                                             +
                 tencounter asc\r                                                                                                                                                                                                                                                                                                                                               +
         ) j\r                                                                                                                                                                                                                                                                                                                                                                  +
 on (v.eid = j.eid)                              \r                                                                                                                                                                                                                                                                                                                             +
   $function$                   

etc., etc., etc., ....

Answer 1

Thanks to @IMSoP for pointing me in the right direction. The following are working scripts I used with psql. No wordprocessor editing is needed!

--------------------------------------------------------
File:  dump_stored_prodecures.sql

SELECT 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 = 'public';

--------------------------------------------------------
File:  dump_procedures.bat

setlocal
del C:\temp\myprocedures
set PGPASSWORD=***********
"C:\Program Files\PostgreSQL\9.3\bin\psql.exe" -h localhost  -At -U postgres -d chaos -f C:/temp/dump_stored_procedures.sql >> C:/temp/myprocedures
pause
endlocal

----------------------------------------------------------
File:  load_procedures.bat

setlocal
del C:\temp\Errors.txt
set PGPASSWORD=*********
"C:\Program Files\PostgreSQL\9.3\bin\psql.exe" -1 -v ON_ERROR_STOP -h xxx.xxx.xxx.xxx -p 5432 -U postgres -d chaos  -f C:/temp/myprocedures  2>> C:/temp/Errors.txt
notepad C:/temp/Errors.txt
endlocal

Useage: In the DOS command prompt, run dump_procedures.bat to create the text file "myprocedures". Then run load_procedures.bat to modify the PostgreSQL databae. Be sure to read the Errors.txt file!

Hope this helps somebody.

Answer 2

The pg_get_functiondef at the top of the file is the column header of the database resultset you've asked psql to output. You can suppress it by using the --tuples-only argument to psql.

The \rs in the output are a representation of a "carriage return" - one half of the "CR LF" convention used by DOS/Windows programs for line breaks, where a Unix/Linux system would use only "LF". I've not been able to reproduce them being there, but a simple if not 100% safe solution would be to replace them away. You can also add a semicolon at the same time:

SELECT replace(pg_get_functiondef(f.oid), '\r', '') || ';'
...

Another option to investigate is using pg_dump in "custom format" mode (-Fc), and then using pg_restore to selectively restore (or generate SQL for) the desired functions. See the docs for pg_restore.