pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
getting a sequence to update properly
2+ messages / 2 participants
[nested] [flat]

* getting a sequence to update properly
@ 2005-06-17 20:04  John Scalia <jscalia@cityblueprinting.com>
  0 siblings, 1 reply; 2+ messages in thread

From: John Scalia @ 2005-06-17 20:04 UTC (permalink / raw)
  To: pgsql-admin

I'm trying to add a sequence function to an already existing field. All the
4000 records for this table currently have a value already in this field,
but I'd like new ones to just get the next value by default as opposed to
users being forced to type something. What I'd like to do is to reload this
table and get the field to be updated by the sequence on the reload. I
thought that by dumping the table with pg_dump and reloading with the
generated datafile, this would happen, but the sequence's last_value field
didn't change. Apparently, the values contained in the dump file won't
update the sequence. Short of thoroughly massaging the datafile generated by
the dump and turning all those record lines into SQL INSERT statements, is
there any way to effectively update a field for an existing record so that
the field gets the value from the sequence? I would  normally just set
minimum value to something greater than what already in the table, but the
manually keyed values are all over the place, and I guess I'd really like to
remove the gaps in the current values.
--
John

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: getting a sequence to update properly
@ 2005-06-18 13:56  Greg Sabino Mullane <greg@turnstep.com>
  parent: John Scalia <jscalia@cityblueprinting.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Greg Sabino Mullane @ 2005-06-18 13:56 UTC (permalink / raw)
  To: pgsql-admin; +Cc: jscalia@cityblueprinting.com


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I would  normally just set minimum value to something greater than what
> already in the table,

That's the way to go.

> but the manually keyed values are all over the place, and I guess I'd
> really like to remove the gaps in the current values.

You'll have to do that yourself, perhaps with a plpgsql function or
an perl script. Here's a quick little function that should do the
job: give it a tablename and a column. The column must contain
unique, positive ids. After running, just slap a default nextval
on the column with a sequence starting at one above the number returned
and you're done.

CREATE OR REPLACE FUNCTION removeholes(text,text) RETURNS INT LANGUAGE plpgsql AS
$$
DECLARE
  T ALIAS FOR $1;
  C ALIAS FOR $2;
  myst TEXT;
  mycount INT := 0;
  myrec RECORD;
BEGIN
  myst := 'UPDATE '||T||' SET '||C||' = -'||C;
  EXECUTE myst;

  myst := 'SELECT '||C||' FROM '||T||' ORDER BY '||C||' DESC ';
  FOR myrec IN EXECUTE myst LOOP
    mycount := mycount + 1;
    myst := 'UPDATE '||T||' SET '||C||' = '||mycount||' WHERE '||C||' = '||myrec.id;
    EXECUTE myst;
  END LOOP;
  RETURN mycount;
END;
$$;

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200506180923
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCtCfbvJuQZxSWSsgRAuBJAJ4q9STr6u6fhssye0l8FitirLZwzQCgwR8g
G2+MqdQquDJz8O3G6zBsVS4=
=MfLV
-----END PGP SIGNATURE-----





^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-06-18 13:56 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-17 20:04 getting a sequence to update properly John Scalia <jscalia@cityblueprinting.com>
2005-06-18 13:56 ` Greg Sabino Mullane <greg@turnstep.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox