Scripting function definitions as SQL?

From: "Postgres User" <postgres(dot)developer(at)gmail(dot)com>
To: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Scripting function definitions as SQL?
Date: 2008-05-11 13:12:40
Message-ID: b88c3460805110612m694e7408ke6acca1ea19a2904@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Has anyone written a function that scripts out all the functions in a
database as full SQL statements (Create Function.....)

I found the below SQL will return all the fields needed to build a SQL
statement, but it would take some work to combine the field values
correctly to get the right format. So does anyone know if the code
has already been written by someone else?

SELECT p.proname AS name, p.oid, p.proargtypes AS args, ds.description
, p.prorettype AS rettype,
p.proretset, p.probin, p.proisstrict AS strict, p.prosrc AS body,
l.lanname AS lang,
u.usename, p.prosecdef, p.provolatile, p.proisagg, n.nspname,
proargnames, p.proargmodes, p.proallargtypes
FROM pg_proc p
LEFT OUTER JOIN pg_description ds ON ds.objoid = p.oid
INNER JOIN pg_namespace n ON p.pronamespace = n.oid
INNER JOIN pg_language l ON l.oid = p.prolang
LEFT OUTER JOIN pg_user u ON u.usesysid = p.proowner
WHERE n.nspname = 'main'
ORDER BY p.proname, n.nspname

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2008-05-11 13:49:37 Re: Scripting function definitions as SQL?
Previous Message Tom Lane 2008-05-11 01:37:29 Re: Using Epoch to save timestamps in 4 bytes?