Re: updating sequence value for column 'serial'

El día miércoles, septiembre 25, 2019 a las 07:42:11a. m. -0700, Adrian Klaver escribió:

> >>> sisis$# DECLARE
> >>> sisis$# maxikatkey integer := ( select max(katkey) from titel_daten );
> >>> sisis$# result integer := 1;
> >>> sisis$# BEGIN
> >>> sisis$# maxikatkey := maxikatkey +1;
> >>> sisis$# RAISE NOTICE '%', maxikatkey ;
> >>> sisis$# result := (SELECT SETVAL('titel_daten_katkey_seq', maxikatkey) );
> >>> sisis$# RAISE NOTICE '%', result ;
> >>> sisis$# END $$;
> >>> NOTICE: 330722
> >>> NOTICE: 330723
> >>> DO
> >>>
> >>> Is there any better way? Thanks
> >>
> >> I have not found a better way. I use ALTER SEQUENCE .. RESTART 330722
> >> though:
> >
> > Yes, but I found no way to use a variable like 'maxikatkey' in the ALTER SEQUENCE ...
> > it only excepts digits like 330722.
>
> DO $$
> DECLARE
> max_id int;
> BEGIN
> SELECT INTO max_id max(id) + 1 FROM seq_test;
> RAISE NOTICE 'Max id is %', max_id;
> EXECUTE 'ALTER SEQUENCE seq_test_id_seq RESTART ' || max_id::text;
> END;
> $$ LANGUAGE plpgsql;

Hi Adrian,

I adopted your code to the name of my table 'ig_target_ipfilter' and its
SERIAL column 'id'; it does not work (and I don't know how it could
works because in the 'ALTER SEQUENCE ...' stmt is somehow missing '... WITH value ...')
or do I understand something wrong?):

cat -n /home/apitzm/postgreSQL/test.sql
1 DO $$
2 DECLARE
3 max_id int;
4 BEGIN
5 SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;
6 RAISE NOTICE 'Max id in % is %', 'ig_target_ipfilter', max_id;
7 EXECUTE 'ALTER SEQUENCE ig_target_ipfilter_id_seq RESTART ' || max_id::text;
8 END;
9 $$ LANGUAGE plpgsql;

psql -Usisis -dsisis < /home/apitzm/postgreSQL/test.sql
NOTICE: Max id in ig_target_ipfilter is <NULL>
ERROR: query string argument of EXECUTE is null
KONTEXT: PL/pgSQL function inline_code_block line 7 at EXECUTE

Please clarify. Thanks

matthias

--
Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub

Mientras haya voluntad de lucha habrá esperanza de vencer.