[postgresql] Show function DDL does't work

Follows: http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=24249

As expected, single function DDL cannot be extracted using pg_dump utility.
Since that, Show function DDL as well as Edit function doesn't work.

Theoretically you may use pg_dump to dump whole database DDL then find and extract the function out. But we all knows it's not good idea, especially in case of large databases.

	Quote:
	As expected, single function DDL cannot be extracted using pg_dump utility.

Could you please elaborate a bit more? An example demonstrating this issue would be very very helpful.

1. In database explorer (or SQLeditor), find functions in objects tree
2. RMB on function item
3. Select Edit Function or Show DDL
4. following error will appear:
/*
Cannot find source code for "public"."add_stat_book".
DDL utility returned error:
pg_dump: No matching tables were found
*/

Note, that table DDL works properly, so all paths (to PostgreSQL_ddl.bat as well as to pg_dump) are correct.
Here is command line run by SQLeditor:
cmd /c d:\SQLASS~3\ddl\POSTGR~1.BAT "dbhost" "5432" "dbuser" "dbpassword" "dbname" "public" "add_chat" "FUNCTION"

Point is, that pg_dump is not intended to extract single function DDL. Don't even know what "FUNCTION" at the end of the command mean.
IMO it's impossible to work with function DDL this way

Thank you.

If you are using PG version 9.x, you can disable in SQL Assistant options the use of pg_dump and make it rely solely on DDL extraction functions available in pg_catalog.
Please have a look at "DDL Code (PostgreSQL)" query in SQL Assistant options, in DB Options tab, you can customize it and add additional code to support all object types you need. The code can be using functions like pg_get_functiondef(oid) see http://www.postgresql.org/docs/9.2/static/functions-info.html
Please be sure to modify the query marked with 9.1 version compatibility. If you are running PG earlier than 8.4, you are out of luck, there are no such functions. Also, there is behavioral difference in different PG versions, if you work with multiple versions you would need to configure multiple versions of "DDL Code (PostgreSQL)"

To disable use of pg_dump, clear "DDL Extraction Utility" filed in SQL Assistant options.

hope this helps.

Thanx,
I made it working for functions but with some issues.
It's enough to add following code at beginning of script for DDL code:

	Code:
	SELECT pg_get_functiondef ( (SELECT oid FROM pg_proc WHERE proname=:OBJECT_NAME AND pronamespace = ( SELECT oid FROM pg_namespace WHERE nspname= :SCHEMA_NAME )) ) UNION ALL /* original code */

But as I said there is the issue (serious one), specific to postgresql. Postgresql functions might be overloaded. It means, more functions with the same name might exists. Those functions might differ by number or type of arguments. So, to identify function in unique way, script have to know OID of function (select oid, * FROM pg_proc). Does SA API provides this value?
Then my code would turn into something like: SELECT pg_get_functiondef(:OID).

Worse is, that by enabling this approach, I lost ability to retrieve table DDL. I was looking around for SQL code to create table DDL but it's not so easy to put it into single query. I found following function which make DDL:

Code:

CREATE OR REPLACE FUNCTION pg_temp.generate_create_table_statement(p_table_name varchar)
RETURNS text AS
$BODY$
DECLARE
v_table_ddl text;
column_record record;
BEGIN
FOR column_record IN
SELECT
b.nspname as schema_name,
b.relname as table_name,
a.attname as column_name,
pg_catalog.format_type(a.atttypid, a.atttypmod) as column_type,
CASE WHEN
(SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
'DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
ELSE
''
END as column_default_value,
CASE WHEN a.attnotnull = true THEN
'NOT NULL'
ELSE
'NULL'
END as column_not_null,
a.attnum as attnum,
e.max_attnum as max_attnum
FROM
pg_catalog.pg_attribute a
INNER JOIN
(SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ ('^('||p_table_name||')$')
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3) b
ON a.attrelid = b.oid
INNER JOIN
(SELECT
a.attrelid,
max(a.attnum) as max_attnum
FROM pg_catalog.pg_attribute a
WHERE a.attnum > 0
AND NOT a.attisdropped
GROUP BY a.attrelid) e
ON a.attrelid=e.attrelid
WHERE a.attnum > 0
AND NOT a.attisdropped
ORDER BY a.attnum
LOOP
IF column_record.attnum = 1 THEN
v_table_ddl:='CREATE TABLE '||column_record.schema_name||'.'||column_record.table_name||' (';
ELSE
v_table_ddl:=v_table_ddl||',';
END IF;

IF column_record.attnum <= column_record.max_attnum THEN
v_table_ddl:=v_table_ddl||chr(10)||
' '||column_record.column_name||' '||column_record.column_type||' '||column_record.column_default_value||' '||column_record.column_not_null;
END IF;
END LOOP;

v_table_ddl:=v_table_ddl||');';
RETURN v_table_ddl;
END;
$BODY$
LANGUAGE 'plpgsql' COST 100.0 SECURITY INVOKER;

But I failed while putting it into your script. I tried anonymous blocks and then call the function as part of UNION but it doesn't work.
Of course I could add this function to DB and then call it from SA, but it is not transparent way enough.

BTW some suggestions for future releases:
1. Split DDL scripts for single objects
2. Add filter 'by database type' to DB Queries list (and maybe others?). When I work with scripts for chosen DB, scrolling/searching through whole list I found disturbing

With regards

Thank you. We are going to take care of the overloaded functions in the next maintenance release or sooner. We are working on altering the code handling PostgreSQL and Redshift db interfaces to use OID as internal object identifiers. This would allow us to deal with overloaded functions and aggregates.

SA recent version still doesn't take care about the issue. It means user has to choose between using pgdump (for tables) or DDL script (for functions). No go for both type of objects.
Because of that I've started to write an extension to DDL Code script. Because I don't know I will find a time to finalize it, I'm attaching it in current state.

It adds table definition with columns, PKs and FKs as well as indexes and triggers. This version requires removing LIMIT 1 statement found at the end of the script. But it is possible to rewrite it the way allowing leaving LIMIT untouched. it's written for postgresql9.2 (for sure it will not work for 8.x and lower). Also it utilizes information_schema which is probably wrong option due to access rights related issues (pg_catalog data should be used instead)
Again, please note script below should be considered as draft version rather than production one.
I believe SA developers finally will take care about the issue.

BTW, writing this script, I noticed that original part of code to generate DDL for triggers doesn't support a few things like disabled triggers and conditional triggers (with WHEN condition)

Code:

SELECT 'CREATE TABLE ' || quote_ident('$SCHEMA_NAME$') || '.' || quote_ident('$OBJECT_NAME$') || E'\n(\n' ||
(SELECT string_agg(columns::TEXT, E',\n')
FROM
(
SELECT ' ' || column_name || ' ' ||
data_type ||
CASE WHEN character_maximum_length IS NULL THEN '' ELSE '(' || character_maximum_length::VARCHAR || ') ' END ||
CASE WHEN data_type = 'smallint' OR data_type = 'integer' OR data_type = 'bigint' OR numeric_precision IS NULL THEN '' ELSE '(' || numeric_precision::VARCHAR || ',' || numeric_scale || ') ' END ||
CASE WHEN coalesce(datetime_precision,6) = 6 THEN '' ELSE '(' || datetime_precision::VARCHAR || ')' END ||
CASE WHEN is_nullable='NO' THEN ' NOT NULL ' ELSE '' END ||
CASE WHEN column_default IS NULL THEN '' ELSE ' DEFAULT ' || column_default END
AS columns
FROM information_schema.columns AS c
WHERE
table_schema = '$SCHEMA_NAME$'
AND table_name = '$OBJECT_NAME$'
ORDER BY c.ordinal_position
) sub)
|| E',\n' ||
coalesce((
SELECT string_agg(constr,E',\n')
FROM
(
SELECT ' CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(c.oid) constr
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype IN ('f', 'p ')
AND n.nspname = '$SCHEMA_NAME$'
AND (conrelid::regclass)::varchar = '$OBJECT_NAME$'
ORDER BY conrelid::regclass::text, contype DESC
) sub2
),'')
|| E'\n);\n\n'

UNION ALL

SELECT string_agg(indexdef, E';\n\n') || E';\n\n'
FROM pg_catalog.pg_indexes
WHERE
schemaname = '$SCHEMA_NAME$'
AND tablename = '$OBJECT_NAME$'

UNION ALL

SELECT string_agg(pg_get_triggerdef(oid,true) || CASE WHEN tgenabled='D' THEN E';\nALTER TABLE ' || quote_ident('$SCHEMA_NAME$') || '.' || quote_ident('$OBJECT_NAME$') || ' DISABLE TRIGGER ' || tgname ELSE '' END, E';\n\n') || E';\n\n'
FROM pg_trigger
WHERE tgname IN
(
SELECT trigger_name FROM information_schema.triggers
WHERE
   event_object_schema = '$SCHEMA_NAME$'
AND event_object_table = '$OBJECT_NAME$'
)

UNION ALL

select 'CREATE VIEW "' || table_schema || '"."' || table_name || '" AS' || chr(10) || view_definition
from information_schema.views
where
table_schema = '$SCHEMA_NAME$'
and table_name = '$OBJECT_NAME$'

UNION ALL

SELECT
'CREATE FUNCTION "' || routine_schema || '"."' || routine_name || '"(' ||
array_to_string(array(
   SELECT COALESCE (p.parameter_name, '') || ' ' || LOWER (p.parameter_mode) || ' ' || REPLACE (p.data_type, '"', '')
FROM information_schema.parameters p
   WHERE p.specific_catalog = r.specific_catalog
      AND p.specific_schema = r.specific_schema
      AND p.specific_name = r.specific_name
   ORDER BY p.ordinal_position
), ',') ||
')' || CHR(10) || ' RETURNS ' || REPLACE (r.data_type, '"', '') ||
' AS' || CHR(10) || '$$' || r.routine_definition
|| '$$' || CHR(10) || 'LANGUAGE ' || r.external_language || ';'
FROM
information_schema.routines r
WHERE
r.routine_schema = '$SCHEMA_NAME$'
AND r.routine_name = :OBJECT_NAME
AND r.external_language in ('SQL', 'PLPGSQL')
AND r.routine_definition <> 'aggregate_dummy'

UNION ALL

SELECT
'CREATE AGGREGATE "' || routine_schema || '"."' || routine_name || '"(' ||
array_to_string(array(
   SELECT COALESCE (p.parameter_name, '') || ' ' || LOWER (p.parameter_mode) || ' ' || REPLACE (p.data_type, '"', '')
FROM information_schema.parameters p
   WHERE p.specific_catalog = r.specific_catalog
      AND p.specific_schema = r.specific_schema
      AND p.specific_name = r.specific_name
   ORDER BY p.ordinal_position
), ',') ||
')' || chr(10) || '(' || chr(10) ||
' SFUNC = ' || pa.aggtransfn || ',' || chr(10) ||
' STYPE = ' || r.data_type ||
   CASE WHEN pa.aggfinalfn <> 0 THEN ',' || chr(10) || ' FINALFUNC = ' || pa.aggfinalfn ELSE '' END ||
CASE WHEN pa.agginitval <> '' THEN ',' || chr(10) || ' INITCOND = ' || pa.agginitval ELSE '' END ||
COALESCE(',' || chr(10) || ' SORTOP = ' || po.oprname, '') ||
chr(10) || ')'
FROM
information_schema.routines r
JOIN pg_catalog.pg_aggregate AS pa
   ON pa.aggfnoid = substring(r.specific_name, '_([^_]*?)$')::oid
LEFT JOIN pg_catalog.pg_operator AS po
ON pa.aggsortop = po.oid
WHERE
r.routine_schema = :SCHEMA_NAME
AND r.routine_name = :OBJECT_NAME

UNION ALL

SELECT 'Code of external routine "'|| routine_name ||'"('|| routine_definition ||') is not available'|| chr(10)
FROM information_schema.routines
WHERE
routine_schema = :SCHEMA_NAME
AND routine_name = :OBJECT_NAME
AND not (external_language in ('SQL', 'PLPGSQL'))
AND routine_definition <> 'aggregate_dummy'

-- LIMIT 1

Last edited by michalk on Mon Dec 21, 2015 10:36 am; edited 1 time in total

You can have different versions of the same query for different versions of PG. Please take a look at the factory default settings. There are two versions of "DDL Code (PostgreSQL)" query. One has its minimal PG version set to 7, the other has its minimal PG version set to 9.1. You can add your version as set the minimum to 9.2.

Do I get it correctly that the version of the code above, the one you posted on 16 Oct 2015 10:02 am is a replacement for the pre-configured "DDL Code (PostgreSQL)" query?
If yes, I can have an enacement request submitted on your part to have it added to the default settings.

Yes, I know that the queries are defined for various db versions.
Indeed, I wrote it as replacement of default DDL code script originally provided for postgresql 9.1. However since I work with 9.2 only, I haven't tested it with 9.1.
For sure it's not final, I'm expect some issues - it is just draft of the solution which main goal is to reject using pgdump. In particular it contains incorrect part regarding functions DDL which I posted about in another thread

with regards

	SysOp wrote:
	Thank you. We are going to take care of the overloaded functions in the next maintenance release or sooner. We are working on altering the code handling PostgreSQL and Redshift db interfaces to use OID as internal object identifiers. This would allow us to deal with overloaded functions and aggregates.

Hello, Show DDL still doesn't work for PostgreSQL tables.
/*
Cannot find source code for "public"."account".
Check the "DDL Code (PostgreSQL)" query in SQL Assistant Options.

*/

Do you have on your roadmap?

UPDATE: now it works, the DDL Tool for Postgresql wasn't set and the PostgreSQL_ddl.bat was set up with a wrong path to the pg_dump. Would it be possible to include pg_dump in SQL Assistant setup?

As far as I know, there is a great deal of differences between different PG versions and pg_dump versions too. A while ago we debated the use of pg_dump, and decided for the time being it's better to use what's installed on the system and expect it to be matching the database server version. If we package a new version of pg_dump, it will not work well with older PG server versions, if we have an older version packaged, it will miss out on all the newest PG features. It's a catch 22 situation. We still hope that PG will catch up to other major databases and provide either a native SQL method or easy to use API to script DDL of database objects, something similar to Oracle DBMS_METADATA.GET_DDL SQL method, or MSSQL SMO API

If you believe there is a good solution to the above issue, please share your thoughts. We welcome everybody to express their thoughts, and more than happy to incorporate your input in the new versions.

SysOp wrote:

As far as I know, there is a great deal of differences between different PG versions and pg_dump versions too. A while ago we debated the use of pg_dump, and decided for the time being it's better to use what's installed on the system and expect it to be matching the database server version. If we package a new version of pg_dump, it will not work well with older PG server versions, if we have an older version packaged, it will miss out on all the newest PG features. It's a catch 22 situation. We still hope that PG will catch up to other major databases and provide either a native SQL method or easy to use API to script DDL of database objects, something similar to Oracle DBMS_METADATA.GET_DDL SQL method, or MSSQL SMO API

If you believe there is a good solution to the above issue, please share your thoughts. We welcome everybody to express their thoughts, and more than happy to incorporate your input in the new versions.

Hello, thank you for answer!

As far as I know, pg_dump is always backward compatible (https://pgolub.wordpress.com/2013/11/19/dump-and-restore-of-postgresql-version-compatibility-faq/), so you can dump the older version of the databases with the newest version of pg_dump, which should cover in my opinion 90% of cases and the rest 10% (or even less) who are using the latest postgresql version should be able to update their pg_dump manually or automatically with the latest SQL Assitant build. Otherwise all 100% can't use DDL extraction by default.

Getting function DDL is easy using available information function
It's enough to add following code to DDL Code script (Options / DB Options / DB Queries / DDL Code), for example after CREATE VIEW

	Code:
	UNION ALL SELECT string_agg(pg_get_functiondef(pp.oid), E'\n\n' ORDER BY pp.oid) FROM pg_catalog.pg_proc pp JOIN pg_catalog.pg_namespace AS pn ON pp.pronamespace=pn.oid WHERE pp.proname = :OBJECT_NAME AND pn.nspname = '$SCHEMA_NAME$'

Of course, to make SA working with this script, you have to switch SA to use DB queries as DDL code source. To do this, set [DB options / SQL Assistance / Postgresql / DB Queries / Query DDL Code] to "DDL code"

But doing this you have to know, this change will affect a way how DDL of tables, sequences and other objects are collected. All those object must be returned by this single SQL script. Since it's not possible to use other constructs than SQL, getting DDL of some complex objects may be not easy.
As example consider a table DDL connected objects (triggers, indexes fks). For example you have to check ownership of field against sequences to show INT or SERIAL datatype etc.
In other cases like showing sequence DDL it's even not possible to retrieve complete DDL this way. I described it in http://www.softtreetech.com/support/phpBB2/viewtopic.php?t=24418
Of course we can write some functions which returns needed data. But it require installing additional code to work with SA which is not always feasible solution.

However in case of sequence wrapper might look like this:

Code:

CREATE OR REPLACE FUNCTION pg_get_sequencedef(seqname VARCHAR, noerror BOOLEAN DEFAULT FALSE)
RETURNS text AS
$BODY$

DECLARE
def TEXT;
BEGIN

EXECUTE
'SELECT ''CREATE SEQUENCE ' || seqname || '
INCREMENT '' || increment_by || ''
MINVALUE '' || min_value || ''
MAXVALUE '' || max_value || ''
START '' || last_value || ''
CACHE '' || cache_value || CASE WHEN is_cycled THEN ''CYCLE'' ELSE '''' END || '';''
FROM ' || seqname
INTO def;

RETURN def;

EXCEPTION
WHEN OTHERS THEN
   IF noerror
   THEN
      RETURN NULL;
   END IF;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION pg_get_sequencedef(VARCHAR, BOOLEAN) OWNER TO postgres;

Here is my recent DDL script for postgresql 9.1+
It can return:
Table (incl. datatype ranges, defaults, primary/foreign keys, (unique) indexes, triggers + disable triggers)
indexes, views, functions, aggregates.
I replaced original snippet for triggers with one which uses system information functions. Thanks to it, we get originally missing features like conditional triggers.

Please note, a sequence related part is commented out, because it returns exception if no object found, which makes it not usable. However putting this code into some wrapper function which just returns no records in case of exception will do the work.

Again: please do not consider it as production code. It covers no all available objects, it may contains bugs etc.

Code:

SELECT 'CREATE TABLE ' || quote_ident('$SCHEMA_NAME$') || '.' || quote_ident(:OBJECT_NAME) || E'\n(\n' ||
(SELECT string_agg(columns::TEXT, E',\n')
FROM
(
SELECT ' ' || quote_ident(column_name) || ' ' ||
CASE -- only for timestamp due to syntax: timestamp(x) without time zone
WHEN udt_name::regtype::varchar ~ '^timestamp' AND coalesce(datetime_precision,6) <> 6 THEN REPLACE(udt_name::regtype::varchar,'timestamp','timestamp(' || datetime_precision::VARCHAR || ')')
ELSE udt_name::regtype::varchar
END ||
CASE WHEN character_maximum_length IS NULL THEN '' ELSE '(' || character_maximum_length::VARCHAR || ') ' END ||
coalesce(CASE WHEN data_type= 'smallint' OR data_type = 'integer' OR data_type = 'bigint' THEN '' ELSE '(' || numeric_precision::VARCHAR || ',' || numeric_scale || ') ' END,'') ||
CASE WHEN is_nullable='NO' THEN ' NOT NULL ' ELSE '' END ||
CASE WHEN column_default IS NULL THEN '' ELSE ' DEFAULT ' || column_default END
AS columns
FROM information_schema.columns AS c
WHERE
table_schema = quote_ident('$SCHEMA_NAME$')
AND table_name = quote_ident(:OBJECT_NAME)
ORDER BY c.ordinal_position
) sub )

||
coalesce(E',\n' || (
SELECT string_agg(constr,E',\n')
FROM
(
SELECT ' CONSTRAINT ' || conname || ' ' || pg_get_constraintdef(c.oid) constr
FROM pg_constraint c
JOIN pg_namespace n ON n.oid = c.connamespace
WHERE contype IN ('f', 'p ')
AND n.nspname = quote_ident('client_champs')
AND (conrelid::regclass)::varchar = quote_ident('client_champs') || '.' || quote_ident('tab_champs')
ORDER BY conrelid::regclass::text, contype DESC
) sub2
),'')
|| E'\n);\n\n'

UNION ALL

SELECT string_agg(indexdef, E';\n\n') || E';\n\n'
FROM pg_catalog.pg_indexes
WHERE
schemaname = '$SCHEMA_NAME$'
AND tablename = '$OBJECT_NAME$'

UNION ALL

SELECT string_agg(pg_get_triggerdef(oid,true) || CASE WHEN tgenabled='D' THEN E';\nALTER TABLE ' || quote_ident('$SCHEMA_NAME$') || '.' || quote_ident('$OBJECT_NAME$') || ' DISABLE TRIGGER ' || tgname ELSE '' END, E';\n\n') || E';\n\n'
FROM pg_trigger
WHERE tgname IN
(
SELECT trigger_name FROM information_schema.triggers
WHERE
   event_object_schema = '$SCHEMA_NAME$'
AND event_object_table = '$OBJECT_NAME$'
)

UNION ALL

select 'CREATE VIEW "' || table_schema || '"."' || table_name || '" AS' || chr(10) || view_definition
from information_schema.views
where
table_schema = '$SCHEMA_NAME$'
and table_name = '$OBJECT_NAME$'

UNION ALL

SELECT string_agg(pg_get_functiondef(pp.oid), E'\n\n' ORDER BY pp.oid)
FROM pg_catalog.pg_proc pp
JOIN pg_catalog.pg_namespace AS pn ON pp.pronamespace=pn.oid
WHERE pp.proname = :OBJECT_NAME
AND pn.nspname = '$SCHEMA_NAME$'

UNION ALL

SELECT
'CREATE AGGREGATE "' || routine_schema || '"."' || routine_name || '"(' ||
array_to_string(array(
   SELECT COALESCE (p.parameter_name, '') || ' ' || LOWER (p.parameter_mode) || ' ' || REPLACE (p.data_type, '"', '')
FROM information_schema.parameters p
   WHERE p.specific_catalog = r.specific_catalog
      AND p.specific_schema = r.specific_schema
      AND p.specific_name = r.specific_name
   ORDER BY p.ordinal_position
), ',') ||
')' || chr(10) || '(' || chr(10) ||
' SFUNC = ' || pa.aggtransfn || ',' || chr(10) ||
' STYPE = ' || r.data_type ||
   CASE WHEN pa.aggfinalfn <> 0 THEN ',' || chr(10) || ' FINALFUNC = ' || pa.aggfinalfn ELSE '' END ||
CASE WHEN pa.agginitval <> '' THEN ',' || chr(10) || ' INITCOND = ' || pa.agginitval ELSE '' END ||
COALESCE(',' || chr(10) || ' SORTOP = ' || po.oprname, '') ||
chr(10) || ')'
FROM
information_schema.routines r
JOIN pg_catalog.pg_aggregate AS pa
   ON pa.aggfnoid = substring(r.specific_name, '_([^_]*?)$')::oid
LEFT JOIN pg_catalog.pg_operator AS po
ON pa.aggsortop = po.oid
WHERE
r.routine_schema = :SCHEMA_NAME
AND r.routine_name = :OBJECT_NAME

UNION ALL

SELECT 'Code of external routine "'|| routine_name ||'"('|| routine_definition ||') is not available'|| chr(10)
FROM information_schema.routines
WHERE
routine_schema = :SCHEMA_NAME
AND routine_name = :OBJECT_NAME
AND not (external_language in ('SQL', 'PLPGSQL'))
AND routine_definition <> 'aggregate_dummy'

/*
UNION ALL

SELECT pg_catalog.pg_get_sequencedef(sequence_schema || '.' || sequence_name)
FROM information_schema.sequences
WHERE sequence_schema = '$SCHEMA_NAME$'
AND sequence_name = :OBJECT_NAME
*/
-- LIMIT 1

Last edited by michalk on Mon Dec 21, 2015 10:59 am; edited 1 time in total

Thank you. I will pass it along.

After comparing your version with the default one, I see that the default one has one additional piece for functions

Code:

UNION ALL

SELECT
'CREATE FUNCTION "' || routine_schema || '"."' || routine_name || '"(' ||
array_to_string(array(
   SELECT COALESCE (p.parameter_name, '') || ' ' || LOWER (p.parameter_mode) || ' ' || REPLACE (p.data_type, '"', '')
FROM information_schema.parameters p
   WHERE p.specific_catalog = r.specific_catalog
      AND p.specific_schema = r.specific_schema
      AND p.specific_name = r.specific_name
   ORDER BY p.ordinal_position
), ',') ||
')' || CHR(10) || ' RETURNS ' || REPLACE (r.data_type, '"', '') ||
' AS' || CHR(10) || '$$' || r.routine_definition
|| '$$' || CHR(10) || 'LANGUAGE ' || r.external_language || ';'
FROM
information_schema.routines r
WHERE
r.routine_schema = :SCHEMA_NAME
AND r.routine_name = :OBJECT_NAME
AND r.external_language in ('SQL', 'PLPGSQL')
AND r.routine_definition <> 'aggregate_dummy'

You may find it helpful.

Yes and no. Original one had a few lacks (for example contained no definer rule etc) and has been no future postgresql compatible.
I replaced it with function pg_get_functiondef

Additionally my script returns all overloaded functions (if exist) as well as is not limited only to sql/plpgsql languages

Also I removed LIMIT 1 from the end of original script. It allows to return multiple objects in one call which is useful especially for table DDL (table with indexes and triggers)