Restore of functional indexes gotcha   

Below is a simple script to recreate the issue so its clear:

CREATE DATABASE superdata;
CREATE OR REPLACE FUNCTION myniftyfunc(myint integer) RETURNS integer AS
 $$ SELECT 1 + $1;$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer AS
$$ SELECT myniftyfunc($1); $$
LANGUAGE 'sql' IMMUTABLE;

CREATE SCHEMA mysuperdata;
CREATE TABLE mysuperdata.mysupertable(sid integer PRIMARY KEY, super_key integer);
CREATE INDEX idx_mysupertable_super_index
   ON mysuperdata.mysupertable USING btree (mysuperniftyfunc(super_key));

INSERT INTO mysuperdata.mysupertable(sid,super_key)
VALUES(1,1);

--Backup superdata
"C:\Program files\postgresql\8.3\bin\pg_dump" --host=localhost --port=5432 --username=postgres --format=plain --verbose --file="C:\superdata.sql" superdata

--Restore
"C:\Program files\postgresql\8.3\bin\psql" -U postgres -h localhost -p 5432 -d superdata2 -f "C:\superduper.sql"

--Get non-super cool error
psql:C:/superduper.sql:99: ERROR:  function myniftyfunc(integer) does not exist
LINE 1:  SELECT myniftyfunc($1);
				^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT myniftyfunc($1);
CONTEXT:  SQL function "mysuperniftyfunc" during startup

Normally I do my backup in compressed format, but did it in plain to demonstrate the problem and here is what pg_dump produces.

--
-- PostgreSQL database dump
--

-- Started on 2009-06-18 21:45:59

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 6 (class 2615 OID 1086067)
-- Name: mysuperdata; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA mysuperdata;


ALTER SCHEMA mysuperdata OWNER TO postgres;

SET search_path = public, pg_catalog;

--
-- TOC entry 21 (class 1255 OID 1086065)
-- Dependencies: 3
-- Name: myniftyfunc(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION myniftyfunc(myint integer) RETURNS integer
	LANGUAGE sql IMMUTABLE
	AS $_$ SELECT 1 + $1;$_$;


ALTER FUNCTION public.myniftyfunc(myint integer) OWNER TO postgres;

--
-- TOC entry 22 (class 1255 OID 1086066)
-- Dependencies: 3
-- Name: mysuperniftyfunc(integer); Type: FUNCTION; Schema: public; Owner: postgres
--

CREATE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer
	LANGUAGE sql IMMUTABLE
	AS $_$ SELECT myniftyfunc($1); $_$;


ALTER FUNCTION public.mysuperniftyfunc(myint integer) OWNER TO postgres;

SET search_path = mysuperdata, pg_catalog;
-- this is not a bug, but would be super
cool if public or whatever the default search path of the database was in here.

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1465 (class 1259 OID 1086068)
-- Dependencies: 6
-- Name: mysupertable; Type: TABLE; Schema: mysuperdata; Owner: postgres; Tablespace:
--

CREATE TABLE mysupertable (
	sid integer NOT NULL,
	super_key integer
);


ALTER TABLE mysuperdata.mysupertable OWNER TO postgres;

--
-- TOC entry 1735 (class 0 OID 1086068)
-- Dependencies: 1465
-- Data for Name: mysupertable; Type: TABLE DATA; Schema: mysuperdata; Owner: postgres
--

COPY mysupertable (sid, super_key) FROM stdin;
1	1
\.

--
-- TOC entry 1734 (class 2606 OID 1086072)
-- Dependencies: 1465 1465
-- Name: mysupertable_pkey; Type: CONSTRAINT; Schema: mysuperdata; Owner: postgres; Tablespace:
--

ALTER TABLE ONLY mysupertable
	ADD CONSTRAINT mysupertable_pkey PRIMARY KEY (sid);


--
-- TOC entry 1732 (class 1259 OID 1086073)
-- Dependencies: 22 1465
-- Name: idx_mysupertable_super_index; Type: INDEX; Schema: mysuperdata; Owner: postgres; Tablespace:
--

CREATE INDEX idx_mysupertable_super_index ON mysupertable USING btree (public.mysuperniftyfunc(super_key));


--
-- TOC entry 1740 (class 0 OID 0)
-- Dependencies: 3
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;


-- Completed on 2009-06-18 21:45:59

--
-- PostgreSQL database dump complete
--

The work around for this situation is to either explicitly qualify the functions you are using within another or for PostgreSQL 8.3 -- add to your function set search_path=public

CREATE OR REPLACE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer AS
$$ SELECT public.myniftyfunc($1); $$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION mysuperniftyfunc(myint integer) RETURNS integer AS
$$ SELECT myniftyfunc($1); $$
LANGUAGE 'sql' IMMUTABLE;

ALTER FUNCTION mysuperniftyfunc(integer) SET search_path=public;

Of course neither of these solutions is particularly satisfying if you are a package author. If you are and that is how this mess started. You want people to be able to install your functions in whatever schema they like and if they wanted to use it globally they would add it to their database default search path. Though that is arguable. Perhaps all packages should live in specifically named schemas.