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