Very nice!
Just load this into the template1 database and it will be available in every database you create after that.
But this doesn't take into account the very reason why I made it a semi-automatic solution. Namely the same sequence can be used on multiple columns.
I guess 99% of the Servoy users probably won't use a sequence for more than 1 column but for that 1% the result won't be satisfactory.
When would you reuse a sequence object? For example in some accounting solution you might want to have a unique serial number covering multiple tables so you reuse a sequence object for this.
Anyway , this is something Michael Fuhr (the guy I reference in the ServoyCamp post) also pointed out.
To solve this you would have to check if a sequence name is returned more than once and act accordingly like so:
- Code: Select all
SELECT COALESCE(MAX(id), 0)+1 AS maxall FROM (
SELECT MAX(tableOneColumn) AS id FROM tableOne
UNION
SELECT MAX(tableTwoColumn) AS id FROM tableTwo
) AS s;
So your actual update SQL will be like this:
- Code: Select all
SELECT setval('sequencename',SELECT COALESCE(MAX(id), 0)+1 AS maxall FROM (SELECT MAX(tableOneColumn) AS id FROM tableOne UNION SELECT MAX(tableTwoColumn) AS id FROM tableTwo) AS s);
So I guess you can script this with a shell script, PL/pgSQL or even in a Servoy method like so:
- Code: Select all
var _sServer = "myConnectionName",
_sQuery = "SELECT n.nspname as _schema, \
c.relname as _table, \
a.attname as _column, \
SUBSTRING(d.adsrc FROM E'nextval\\\\(''([^'')]+)''') AS _seq \
FROM pg_attrdef AS d \
JOIN pg_attribute AS a ON a.attrelid = d.adrelid AND a.attnum = d.adnum \
JOIN pg_class AS c ON c.oid = d.adrelid \
JOIN pg_namespace AS n ON n.oid = c.relnamespace \
WHERE adsrc LIKE 'nextval(''%' \
ORDER BY _seq",
_ds = databaseManager.getDataSetByQuery(_sServer, _sQuery, null, -1),
_aGetMax = new Array(),
_sUpdateSQL,
_oException;
if (_ds.getException()) {
application.output("Error:" + _ds.getException().getMessage(), LOGGINGLEVEL.ERROR);
} else {
for (var i = 1; i <= _ds.getMaxRowIndex(); i++) {
_aGetMax.push("SELECT MAX(" + _ds.getValue(i, 3) + ") AS maxid FROM " + _ds.getValue(i, 1) + "." + _ds.getValue(i, 2));
if (i == _ds.getMaxRowIndex() || _ds.getValue(i + 1, 4) != _ds.getValue(i, 4)) {
_sUpdateSQL = "SELECT setval('" + _ds.getValue(i, 4) + "', (SELECT COALESCE(MAX(maxid), 0)+1 AS maxall FROM (" + _aGetMax.join(" UNION ") + ") AS t ));";
_oException = databaseManager.getDataSetByQuery(_sServer, _sUpdateSQL, null, 1).getException();
if (_oException) {
application.output("Error:" + _oException.getMessage(), LOGGINGLEVEL.ERROR);
application.output(_sUpdateSQL, LOGGINGLEVEL.ERROR);
}
_aGetMax = new Array();
}
}
}
UPDATE: I removed the use of the rawSQL plugin in the method. Since we use a SELECT this can be done with a regular getDataSetByQuery.