Hi,
if anybody needs something like this here is the request to produce the code to
resynch SEQUENCEs with the data in tha tables:
SELECT 'SELECT SETVAL(\'' ||S.relname|| '\', MAX(' ||C.attname|| ') ) FROM '
||T.relname|| ' ;'
FROM pg_class AS S, pg_depend AS D, pg_class AS T, pg_attribute AS C
WHERE S.relkind = 'S'
AND S.oid = D.objid
AND D.refobjid = T.oid
AND D.refobjid = C.attrelid
AND D.refobjsubid = C.attnum
ORDER BY S.relname
This goes through all the sequences in current shema and generates SELECT
SETVAL(... orders to set the SEQUENCE to the last value in the table.
This only works it the SEQUENCE is OWNED (ALTER SEQUENCE ... OWNED BY ... since
8.2 or SERIAL) by a column: this way, the above SELECT will "find" the matching
column and table for a SEQUENCE.
Note : this will not work for SEQUENCES not linked to tables, you could find a
way by working out the table name and column name from the sequence name (for
instance by using a similar naming convention to the SERIAL sequences).
Thanks for all the help I got.
Cheers,
[EMAIL PROTECTED]
The Computing Froggy
----- Message d'origine ----
De : Alvaro Herrera <[EMAIL PROTECTED]>
À : Laurent ROCHE <[EMAIL PROTECTED]>
Cc : Martijn van Oosterhout <[EMAIL PROTECTED]>; pgsql-general@postgresql.org
Envoyé le : Jeudi, 18 Octobre 2007, 23h54mn 16s
Objet : Re: Re : Re : [GENERAL] Resetting SEQUENCEs
Laurent ROCHE wrote:
> Hi,
>
> So nobody can help me to write the SELECT that will return the
SEQUENCE names, and their linked columns and their linked tables ?
> Are the system tables documented somewhere ?
Of course -- in the "internals" section. You need pg_class where
relkind = 's', pg_depend, and possibly pg_attribute.
--
Alvaro Herrera
http://www.PlanetPostgreSQL.org/
"La naturaleza, tan frágil, tan expuesta a la muerte... y tan viva"
---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
_____________________________________________________________________________
Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
