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,
[EMAIL PROTECTED]
The Computing Froggy
----- Message d'origine ----
De : Filip Rembiałkowski <[EMAIL PROTECTED]>
À : Laurent ROCHE <[EMAIL PROTECTED]>
Cc : [email protected]
Envoyé le : Jeudi, 18 Octobre 2007, 19h33mn 50s
Objet : Re: [GENERAL] Resetting SEQUENCEs
2007/10/18, Laurent ROCHE <[EMAIL PROTECTED]>:
>
> 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 !
