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
[email protected]<mailto:[email protected]>
[cid:[email protected]] orbitz.com<http://www.orbitz.com/> |
ebookers.com<http://www.ebookers.com/> |
hotelclub.com<http://www.hotelclub.com/> |
cheaptickets.com<http://www.cheaptickets.com/> |
ratestogo.com<http://www.ratestogo.com/> |
asiahotels.com<http://www.asiahotels.com/>
<<inline: image001.jpg>>
