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

8.109. slon_quote_input(text)

Function Properties

PLPGSQLtext
quote all words that aren't quoted yet
      declare
         p_tab_fqname alias for $1;
         v_nsp_name text;
         v_tab_name text;
    	 v_i integer;
    	 v_l integer;
         v_pq2 integer;
    begin
    	v_l := length(p_tab_fqname);
    
    	-- Let us search for the dot
    	if p_tab_fqname like '"%' then
    		-- if the first part of the ident starts with a double quote, search
    		-- for the closing double quote, skipping over double double quotes.
    		v_i := 2;
    		while v_i <= v_l loop
    			if substr(p_tab_fqname, v_i, 1) != '"' then
    				v_i := v_i + 1;
    			else
    				v_i := v_i + 1;
    				if substr(p_tab_fqname, v_i, 1) != '"' then
    					exit;
    				end if;
    				v_i := v_i + 1;
    			end if;
    		end loop;
    	else
    		-- first part of ident is not quoted, search for the dot directly
    		v_i := 1;
    		while v_i <= v_l loop
    			if substr(p_tab_fqname, v_i, 1) = '.' then
    				exit;
    			end if;
    			v_i := v_i + 1;
    		end loop;
    	end if;
    
    	-- v_i now points at the dot or behind the string.
    
    	if substr(p_tab_fqname, v_i, 1) = '.' then
    		-- There is a dot now, so split the ident into its namespace
    		-- and objname parts and make sure each is quoted
    		v_nsp_name := substr(p_tab_fqname, 1, v_i - 1);
    		v_tab_name := substr(p_tab_fqname, v_i + 1);
    		if v_nsp_name not like '"%' then
    			v_nsp_name := '"' || replace(v_nsp_name, '"', '""') ||
    						  '"';
    		end if;
    		if v_tab_name not like '"%' then
    			v_tab_name := '"' || replace(v_tab_name, '"', '""') ||
    						  '"';
    		end if;
    
    		return v_nsp_name || '.' || v_tab_name;
    	else
    		-- No dot ... must be just an ident without schema
    		if p_tab_fqname like '"%' then
    			return p_tab_fqname;
    		else
    			return '"' || replace(p_tab_fqname, '"', '""') || '"';
    		end if;
    	end if;
    
    end;

Google
Contact me at cbbrowne@acm.org