Thread: setting last_value of sequence

i've run into the situation where i need to set the last_value of a
sequence. can someone tell me how this is done?

--
--------------------------------------------------------------------------
                                               | /"\
 john harrold                                  | \ / ASCII ribbon campaign
      jmh at member.fsf.org                    |  X  against HTML mail
           the most useful idiot               | / \
--------------------------------------------------------------------------
 What difference does it make to the dead, the orphans, and the homeless,
 whether the mad destruction is brought under the name of totalitarianism or
 the holy name of liberty and democracy?
 --Gandhi
--------------------------------------------------------------------------
gpg --keyserver keys.indymedia.org --recv-key F65A739E
--------------------------------------------------------------------------

Perhaps:
SELECT SETVAL('resrc_serial', MAX(resource_id)) FROM ia_resources;
               the sequence        the column          the table
This sets the sequence to the highest number after I have used "copy" to load a table; other values instead of MAX()
couldbe used (e.g. 123456, etc.). 

HTH,

Greg Williamson

-----Original Message-----
From: John Harrold [mailto:jmh17@pitt.edu]
Sent: Tuesday, September 02, 2003 2:01 PM
To: pgsql general list
Subject: [GENERAL] setting last_value of sequence


i've run into the situation where i need to set the last_value of a
sequence. can someone tell me how this is done?

--
--------------------------------------------------------------------------
                                               | /"\
 john harrold                                  | \ / ASCII ribbon campaign
      jmh at member.fsf.org                    |  X  against HTML mail
           the most useful idiot               | / \
--------------------------------------------------------------------------
 What difference does it make to the dead, the orphans, and the homeless,
 whether the mad destruction is brought under the name of totalitarianism or
 the holy name of liberty and democracy?
 --Gandhi
--------------------------------------------------------------------------
gpg --keyserver keys.indymedia.org --recv-key F65A739E
--------------------------------------------------------------------------

John Harrold wrote:
-- Start of PGP signed section.
> i've run into the situation where i need to set the last_value of a
> sequence. can someone tell me how this is done?

setval()?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Or

-- Function: public.set_sequence(name, int4)
CREATE FUNCTION public.set_sequence(name, int4) RETURNS int4 AS '
DECLARE
  l_sequence_name ALIAS FOR $1;
  l_last_value ALIAS FOR $2;
BEGIN
  IF  l_last_value = 0 THEN
    PERFORM setval(l_sequence_name,1, False);
  ELSE
    PERFORM setval(l_sequence_name,l_last_value);
  END IF;
RETURN 1;
END;'  LANGUAGE 'plpgsql' VOLATILE;


> Perhaps:
> SELECT SETVAL('resrc_serial', MAX(resource_id)) FROM ia_resources;
>                the sequence        the column          the table
> This sets the sequence to the highest number after I have used "copy"
> to load a table; other values instead of MAX() could be used (e.g.
> 123456, etc.).
> Greg Williamson
>
> -----Original Message-----
> From: John Harrold [mailto:jmh17@pitt.edu]
> Sent: Tuesday, September 02, 2003 2:01 PM
> To: pgsql general list
> Subject: [GENERAL] setting last_value of sequence
>
>
> i've run into the situation where i need to set the last_value of a
> sequence. can someone tell me how this is done?
>
> --
> --------------------------------------------------------------------------
>                                                | /"\
>  john harrold                                  | \ / ASCII ribbon
> campaign
>       jmh at member.fsf.org                    |  X  against HTML mail
>            the most useful idiot               | / \
> --------------------------------------------------------------------------
>  What difference does it make to the dead, the orphans, and the
> homeless, whether the mad destruction is brought under the name of
> totalitarianism or the holy name of liberty and democracy?
>  --Gandhi
> --------------------------------------------------------------------------
> gpg --keyserver keys.indymedia.org --recv-key F65A739E
> --------------------------------------------------------------------------
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 2: you can get off all lists
> at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)


~Berend Tober