On 9/25/19 10:12 PM, Matthias Apitz wrote:
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
I forgot about the possibility of NULL being returned by max_id in:
SELECT INTO max_id max(id) + 1 FROM ig_target_ipfilter ;
So:
SELECT INTO max_id COALESCE(max(id), 0) + 1 FROM ig_target_ipfilter ;
That will turn a NULL max(id) into 0.
matthias
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
