Function to dump function ddl - Mailing list pgsql-general

Hi,

In deploying new versions of function source, I want to export the current definition to a file.

After looking around it seems that I needed to create my own function.

I got the function to work, but am having a slight problem with the execution of the exported file.

It seems that if I include line breaks they are not being interpreted by psql on input.

Other than leaving the line breaks out,  does anybody have any suggestions for getting psql to ignore the line feeds.

I tried to replace the \n with blanks on output, but failed.

Some specifics might help.

Running on redhat,  pg 8.2.15/greenplum 4.1.2.6

Ddl export

d1gp1=> \copy (SELECT  dba_work.pg_get_functiondef('da_test.aggairbookingitinasbookedprc'::regproc)) to test1.sql

d1gp1=>

exported file

[dlittle@eginformatica02p ~]$ cat test1.sql

\nCREATE OR REPLACE FUNCTION da_test.aggairbookingitinasbookedprc(numeric)\nRETURNS void AS\n$BODY$\n\n/* Declare Variables*/\n-- modified 2/28/2010\nDECLARE\n    vTimestamp TIMESTAMP(6);    -- Variable to Store Current Timestamp during diffent process stage.\n    vAuditKey VARCHAR(18);              -- Variable to Capture Lastest Auditkey for Specific Dimension Processing.\n    vSpStep INTEGER;

Import

d1gp1=> \i test1.sql

psql:test1.sql:1: invalid command \nCREATE

d1gp1=>

--

Replacing \n attempt

d1gp1=> \copy (select replace(dba_work.pg_get_functiondef('da_test.aggairbookingitinasbookedprc'::regproc),E'\n',' ')) to test1.sql WARNING:  nonstandard use of escape in a string literal

LINE 1: ...test.aggairbookingitinasbookedprc' ::regproc ) ,E '\n' , ' '...

                                                             ^

HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.

ERROR:  type "e" does not exist

LINE 1: ...a_test.aggairbookingitinasbookedprc' ::regproc ) ,E '\n' , '...

                                                             ^

\copy: ERROR:  type "e" does not exist

LINE 1: ...a_test.aggairbookingitinasbookedprc' ::regproc ) ,E '\n' , '...

d1gp1=> \copy (select replace(dba_work.pg_get_functiondef('da_test.aggairbookingitinasbookedprc'::regproc),'\\n',' ')) to test1.sql WARNING:  nonstandard use of \\ in a string literal

LINE 1: ..._test.aggairbookingitinasbookedprc' ::regproc ) , '\\n' , ' ...

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com