Function to dump function ddl

Started by Little, Douglasover 13 years ago2 messagesgeneral

DOUGLAS.LITTLE@orbitz.com

over 13 years ago

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<mailto:Douglas.Little@orbitz.com>
[cid:image001.jpg@01CD7625.A87D1690] 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/>

Laurenz Albe

laurenz.albe@cybertec.at

over 13 years ago

In reply to: Little, Douglas (#1)

Re: Function to dump function ddl

Douglas Little wrote:

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'::regp
roc))

to test1.sql

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;

Using COPY for that is not the best choice, as you see.

If you do it from psql, why not use \o?

test=> \pset tuples_only on
test=> \pset format unaligned
test=> \o func.sql
test=> SELECT pg_get_functiondef('first'::regproc);
test=> \q

$ cat func.sql
CREATE OR REPLACE FUNCTION laurenz.first(text, integer)
RETURNS integer
LANGUAGE sql
STABLE STRICT
AS $function$SELECT id FROM test WHERE val=$1 LIMIT $2$function$

Does that help?

Yours,
Laurenz Albe

Function to dump function ddl

Attachments: