SQL Queries - Common SQL Queries - Queries on Installation - Update the Unique Service ID

set search_path = <Schema Name>; 
 
DO $$
DECLARE 
rec record;
curschema varchar;
oldsiteid integer;
qry1 varchar;
newsiteid INTEGER;
 
BEGIN
	DROP TABLE IF EXISTS tmp_otherschema_sys_site;
	CREATE TEMP TABLE tmp_otherschema_sys_site(site_id INTEGER NOT NULL);
	SELECT current_schema() into curschema;
	SELECT site_id FROM sys_site into oldsiteid;
	--RAISE NOTICE 'Old site id  %',oldsiteid;
	
	FOR rec in SELECT table_schema FROM information_schema.tables WHERE table_name = 'sys_site' and table_schema <> curschema 
	LOOP
		
		SELECT format('SELECT site_id FROM %s.sys_site', quote_ident(rec.table_schema)) into qry1;
		Execute 'INSERT INTO tmp_otherschema_sys_site (site_id) ' || qry1;		
		
        END LOOP;
        
        IF EXISTS(SELECT 1 FROM tmp_otherschema_sys_site WHERE site_id = oldsiteid) THEN
			
		SELECT (CASE WHEN MIN(site_id) > 1 then MIN(site_id)-1 ELSE MAX(site_id)+1 END)::INTEGER into newsiteid 
		  FROM tmp_otherschema_sys_site;
		--RAISE NOTICE 'New site id  %',newsiteid;
		UPDATE sys_site SET site_id = newsiteid WHERE site_id = oldsiteid;
		
	END IF;                    
	DROP TABLE IF EXISTS tmp_otherschema_sys_site;
	RETURN;
 END$$;