Re : Resetting SEQUENCEs

It is not as easy as that :

1/ select table_name from information_schema.tables does not give you the tables list on the current schema but the list of all objects !
But the following will
SELECT c.relname
FROM pg_namespace AS nc, pg_class AS c
WHERE c.relnamespace = nc.oid
AND c.relkind IN ('r' )

2/ Having the tables list is not enough (because obviously I know how to do this), then you need to get the list of sequences (actually you want to get first the sequences) and then the list of colums using the sequence and (at last the tables where the columns are) .
At that point, it is possible to generate the SQL order to reset the SEQUENCE.

Unfortunately I do not know how to produce the list of sequences and columns using them and columns tables.

Have fun,
L@u
The Computing Froggy

----- Message d'origine ----
De : Filip Rembiałkowski <plk.zuber@gmail.com>
À : Laurent ROCHE <laurent_roche@yahoo.com>
Cc : pgsql-general@postgresql.org
Envoyé le : Jeudi, 18 Octobre 2007, 19h33mn 50s
Objet : Re: [GENERAL] Resetting SEQUENCEs

2007/10/18, Laurent ROCHE <laurent_roche@yahoo.com>:

Hi,

I am quite surprised I could not find a way to automatically reset

the value

of a sequence for all my tables.

Of course, I can write:
SELECT setval('serial', max(id)) FROM distributors
But if I reload data into all my tables, it's a real pain to have to

write

something like this for every single table with a sequence.

I would expect PostgreSQL to provide some command like:
resynchAllSequences my_schema;

try something like

CREATE FUNCTION execute(in_sql TEXT) RETURNS void as $$
BEGIN
EXECUTE in_sql;
RETURN;
END;
$BODY$ language plpgsql;

select execute(
$$select setval( '$$
|| table_name ||
$$_id_seq', coalesce((select max(id) from $$
|| table_name ||
$$),1), false ) $$
) from information_schema.tables where you want;

--
Filip Rembiałkowski

---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

______________________________________________________________________________
Stockage illimité de vos mails avec Yahoo! Mail. Changez aujourd'hui de mail !