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
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.
L@u
The Computing Froggy
----- Message d'origine ----
De : Filip Rembiałkowski <plk.zuber@xxxxxxxxx>
À : Laurent ROCHE <laurent_roche@xxxxxxxxx>
Cc : pgsql-general@xxxxxxxxxxxxxx
Envoyé le : Jeudi, 18 Octobre 2007, 19h33mn 50s
Objet : Re: Resetting SEQUENCEs
2007/10/18, Laurent ROCHE <laurent_roche@xxxxxxxxx>:
>
> 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
De : Filip Rembiałkowski <plk.zuber@xxxxxxxxx>
À : Laurent ROCHE <laurent_roche@xxxxxxxxx>
Cc : pgsql-general@xxxxxxxxxxxxxx
Envoyé le : Jeudi, 18 Octobre 2007, 19h33mn 50s
Objet : Re: Resetting SEQUENCEs
2007/10/18, Laurent ROCHE <laurent_roche@xxxxxxxxx>:
>
> 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 !
