Christopher B. Browne's Home Page
cbbrowne@acm.org

8.47. determineidxnameunique(text, name)

Function Properties

PLPGSQLname
FUNCTION determineIdxnameUnique (tab_fqname, indexname) Given a tablename, tab_fqname, check that the unique index, indexname, exists or return the primary key index name for the table. If there is no unique index, it raises an exception.
    declare
    	p_tab_fqname	alias for $1;
    	v_tab_fqname_quoted	text default '';
    	p_idx_name		alias for $2;
    	v_idxrow		record;
    begin
    	v_tab_fqname_quoted := slon_quote_input(p_tab_fqname);
    	--
    	-- Ensure that the table exists
    	--
    	if (select PGC.relname
    				from "pg_catalog".pg_class PGC,
    					"pg_catalog".pg_namespace PGN
    				where slon_quote_brute(PGN.nspname) || '.' ||
    					slon_quote_brute(PGC.relname) = v_tab_fqname_quoted
    					and PGN.oid = PGC.relnamespace) is null then
    		raise exception 'Slony-I: determineIdxnameUnique(): table % not found', v_tab_fqname_quoted;
    	end if;
    
    	--
    	-- Lookup the tables primary key or the specified unique index
    	--
    	if p_idx_name isnull then
    		select PGXC.relname
    				into v_idxrow
    				from "pg_catalog".pg_class PGC,
    					"pg_catalog".pg_namespace PGN,
    					"pg_catalog".pg_index PGX,
    					"pg_catalog".pg_class PGXC
    				where slon_quote_brute(PGN.nspname) || '.' ||
    					slon_quote_brute(PGC.relname) = v_tab_fqname_quoted
    					and PGN.oid = PGC.relnamespace
    					and PGX.indrelid = PGC.oid
    					and PGX.indexrelid = PGXC.oid
    					and PGX.indisprimary;
    		if not found then
    			raise exception 'Slony-I: table % has no primary key',
    					v_tab_fqname_quoted;
    		end if;
    	else
    		select PGXC.relname
    				into v_idxrow
    				from "pg_catalog".pg_class PGC,
    					"pg_catalog".pg_namespace PGN,
    					"pg_catalog".pg_index PGX,
    					"pg_catalog".pg_class PGXC
    				where slon_quote_brute(PGN.nspname) || '.' ||
    					slon_quote_brute(PGC.relname) = v_tab_fqname_quoted
    					and PGN.oid = PGC.relnamespace
    					and PGX.indrelid = PGC.oid
    					and PGX.indexrelid = PGXC.oid
    					and PGX.indisunique
    					and slon_quote_brute(PGXC.relname) = slon_quote_input(p_idx_name);
    		if not found then
    			raise exception 'Slony-I: table % has no unique index %',
    					v_tab_fqname_quoted, p_idx_name;
    		end if;
    	end if;
    
    	--
    	-- Return the found index name
    	--
    	return v_idxrow.relname;
    end;

Google
Contact me at cbbrowne@acm.org