No DDL for sequences

After I started experiments to extend script for DDLs I found the default script doesn't provide DDL for sequences.
Here are sql code to be added to DDL script (might be at the end, before LIMIT 1 which should be removed IMHO).

	Code:
	UNION ALL SELECT 'CREATE SEQUENCE ' \|\| sequence_name \|\| ' INCREMENT ' \|\| increment_by \|\| ' MINVALUE ' \|\| min_value \|\| ' MAXVALUE ' \|\| max_value \|\| ' START ' \|\| last_value \|\| ' CACHE ' \|\| cache_value \|\| CASE WHEN is_cycled THEN 'CYCLE' ELSE '' END \|\| ';' FROM $SCHEMA_NAME$.$OBJECT_NAME$

Wonderful. Thank you so much. I will submit that enhancement request for the next release.

Unfortunately it's not as fine as it seems to be.
This query fails if sequence with given name doesn't exist in database. That means whole DDL query fails everytime we are looking for other objects than sequences.
I even consulted the problem with postgresql gurus and looks like it's impossible to create sequence DDL the way it works in SA.

There is information_schema.sequences view, but it provides no current value. curval() function is also a dead-end: the call have to be preceded by nexval() which we don't want to do.
I tried to create temporary function before calling the UNION query, but it doesn't work. However making it possible could do the trick.

My suggestion for future releases: make possible to define DDL code SQL script for each type object separately.

Try this one

Code:

SELECT 'CREATE SEQUENCE ' || s.sequence_schema || '.' || s.sequence_name || '
INCREMENT ' || increment || '
' || CASE WHEN s.minimum_value = s.start_value THEN 'NO MINVALUE' ELSE 'MINVALUE ' || s.minimum_value END || '
' || CASE WHEN s.maximum_value = '9223372036854775807' THEN 'NO MAXVALUE' ELSE 'MAXVALUE ' || s.maximum_value END || '
START WITH ' || s.start_value || '
' || CASE s.cycle_option WHEN 'YES' THEN 'CYCLE' ELSE 'NO CYCLE' END ||
CASE WHEN a.attname IS NULL THEN '' ELSE '
OWNED BY ' || t.relname || '.' || a.attname END || ';'
FROM information_schema.sequences AS s
JOIN pg_catalog.pg_class AS c
ON c.relkind = 'S'
AND c.relname = s.sequence_name
JOIN pg_catalog.pg_namespace AS n
ON n.oid = c.relnamespace
AND n.nspname = s.sequence_schema
LEFT JOIN pg_catalog.pg_depend AS d
on d.refobjid = c.oid
AND d.deptype = 'n'
LEFT JOIN pg_catalog.pg_attrdef AS ad
ON ad.oid = d.objid
LEFT JOIN pg_catalog.pg_class AS t
ON t.relnamespace = c.relnamespace
AND t.oid = ad.adrelid
LEFT JOIN pg_catalog.pg_attribute AS a
ON a.attnum = ad.adnum
AND a.attrelid = t.oid
AND a.attnum > 0
WHERE s.sequence_schema = '$SCHEMA_NAME$'
AND s.sequence_name = '$OBJECT_NAME$'

DDL from pgadmin

	Code:
	CREATE SEQUENCE szn_omezeni_id_omezeni_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 8 CACHE 1;

Result of your query

	Code:
	CREATE SEQUENCE public.szn_omezeni_id_omezeni_seq INCREMENT 1 NO MINVALUE NO MAXVALUE START WITH 1 NO CYCLE OWNED BY szn_omezeni.id_omezeni;

I don't know if NO MINVALUE and NO MAXVALUE are real substitutes of the values presented in information schema, but start_value IS NOT representing current value.
As I said in previous post, there is no possibility to get current value from information_schema.sequences view. And using currval() cannot be used as well :(

In the meantime I solved it on my end this way:

1. I added following function to my databases:

Code:

CREATE OR REPLACE FUNCTION pg_catalog.pg_get_sequencedef(seqname VARCHAR)
RETURNS TEXT
AS
$$

/*
* By Michal Kozusznik
*/

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;
END;
$$
LANGUAGE plpgsql;

2. Then I modified DDL code script adding:

	Code:
	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

Now it works as expected. Of course it is not suggested way for commercial product to force users to modify their databases.
However if you manage to run more than one statement from single SQL script over ODBC, you can add pg_get_sequencedef function at beginning of the DDL code script, creating it in pg_temp schema. Then call it from pg_temp schema later in UNION query. Creating function in pg_temp schema is equivalent of creating temporary function which stays created until session is closed.

I'm glad you developed a solution that's working well for you. In response to the previous message

	Quote:
	but start_value IS NOT representing current value

that's correct, the query was designed to return the original start value specified explicitly or as a default value in the sequence CREATE command. If you want to use the current value, not the original start value, then you can simply replace s.start_value with ( SELECT currval(s.sequence_schema || '.' || s.sequence_name) ), as simple as that. Similarly, if you want to use hard-coded default minimum and maximum values, simply remove the CASE constructs and reference s.minimum_value and s.maximum._value, but that may lead to an issue if you use cycling option and output current start value instead of the original start value

Actually currval doesn't work as you seem it does.
Here is a quote from postgresql documentation:

	Quote:
	Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.

Long story short, currval is unusable in our case.