On Mon, Oct 11, 2004 at 10:28:22AM +0100, Richard Huxton wrote: > One thing you might want to test is what happens when you manually > create a sequence separate from a table, i.e. no such table-name exists.
Instead of querying pg_statio_user_sequences, you could get the sequences from pg_attrdef if you want to update only sequences that are used in a DEFAULT expression. I'd also improve on the original by joining against pg_class and pg_attribute to get the actual table and column names instead of parsing them from the sequence name, which might yield bogus results if a table or column has been renamed. Here's an attempt at the query I'd make: SELECT n.nspname, c.relname, a.attname, SUBSTRING(d.adsrc FROM 'nextval\\(''([^'')]+)''') AS seqname 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 seqname; This query should return all sequences used in a DEFAULT expression, whether implicitly via a SERIAL type or via an explicit nextval(). It should also return the correct schema, table, and column names. > Also, you can have more than one table relying on a single sequence (and > I have in one of my systems). Not sure there's anything useful you can > do in such a case, or how you'd detect such a situation. The above query should return all tables and columns that reference the sequence. You could get the MAX of all of them by building a UNION query: SELECT COALESCE(MAX(MAX), 0) AS maxall FROM ( SELECT MAX(fooid) FROM foo UNION SELECT MAX(barid) FROM bar ) AS s; Building such a query would be easy in Perl or Python. The OP said he'd like to see a plpythonu implementation so maybe I'll whip one up if I get time. I'd be inclined to just write an ordinary Python script instead of a stored procedure, however, so it could be used on systems that didn't have plpythonu. -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly