"Regina" <robe.dnd@cityofboston.gov> writes:
> If I have a functional index in place in a table in non-public schema that
> uses a function in public schema, these indexes never get restored.
Works for me (per attached). Please provide a *complete* example and
not an abstraction.
regards, tom lane
$ psql regression
psql (8.4rc1)
Type "help" for help.
regression=# create database test;
CREATE DATABASE
regression=# \c test
psql (8.4rc1)
You are now connected to database "test".
test=# create function foo(int) returns int as 'select $1+1' language sql
test-# strict immutable;
CREATE FUNCTION
test=# create schema s1;
CREATE SCHEMA
test=# create table s1.t1 (f1 int);
CREATE TABLE
test=# create index i1 on s1.t1 (foo(f1));
CREATE INDEX
test=# \d s1.t1
Table "s1.t1"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
Indexes:
"i1" btree (foo(f1))
test=# \q
$ pg_dump test >test.dump
$ psql regression
psql (8.4rc1)
Type "help" for help.
regression=# create database test2;
CREATE DATABASE
regression=# \c test2
psql (8.4rc1)
You are now connected to database "test2".
test2=# \i test.dump
SET
SET
SET
SET
SET
SET
CREATE SCHEMA
ALTER SCHEMA
SET
CREATE FUNCTION
ALTER FUNCTION
SET
SET
SET
CREATE TABLE
ALTER TABLE
CREATE INDEX
REVOKE
REVOKE
GRANT
GRANT
test2=# \d s1.t1
Table "s1.t1"
Column | Type | Modifiers
--------+---------+-----------
f1 | integer |
Indexes:
"i1" btree (public.foo(f1))
test2=#