[Pdns-users] auto-incrementing serial numbers for postgresql..

[Aaron Sethman]

Seeing that there was no automatic way to do this in powerdns with
postgresql as a back end.  I wrote a little trigger to do this, and I
figured somebody here would find it useful.  This code is kinda ugly but
it works here at least.  It increments the serial number by one on each
UPDATE or INSERT on the records table.

Regards,

Aaron


CREATE OR REPLACE FUNCTION update_soa() RETURNS TRIGGER AS '
DECLARE
	s1 text;
	s2 text;
	s3 int4;
	s4 text;
	s5 text;
	s6 text;
	s7 text;
	str text;
	ctx text;
BEGIN
	IF NEW.type = ''SOA'' THEN
		RETURN NEW;
	END IF;

	SELECT name INTO str FROM domains WHERE id = NEW.domain_id AND type = ''MASTER'' OR type = ''NATIVE'';
	IF NOT FOUND THEN
		RETURN NEW;
	END IF;


	SELECT content INTO ctx FROM records WHERE domain_id = NEW.domain_id AND type = ''SOA'';
	s1 := split_part(ctx, '' '',1);
	s2 := split_part(ctx, '' '',2);
	s3 := split_part(ctx, '' '',3)::int4;
	s3 := s3+1;
	s4 := split_part(ctx, '' '',4);
	s5 := split_part(ctx, '' '',5);
	s6 := split_part(ctx, '' '',6);
	s7 := split_part(ctx, '' '',7);


	UPDATE records SET content =
	(s1 || '' '' || s2 || '' '' || s3 || '' '' || s4 || '' '' || s5 || '' '' || s6 || '' '' || s7)
	WHERE domain_id = NEW.domain_id AND type = ''SOA'';
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

DROP TRIGGER update_soa ON records;
CREATE TRIGGER update_soa BEFORE INSERT OR UPDATE ON records FOR EACH ROW EXECUTE PROCEDURE update_soa();