Re: Dump/restore indexes and functions in public schema

marian krucina <marian(dot)krucina(at)gmail(dot)com> writes:
> Example:
> in PG91:
> CREATE FUNCTION function_y(x INT) RETURNS INT AS $$ SELECT $1*$1 $$
> LANGUAGE SQL;
> CREATE FUNCTION function_x(x INT) RETURNS INT AS $$ SELECT
> function_y($1) $$ LANGUAGE SQL;
> CREATE SCHEMA schema_a;
> CREATE TABLE schema_a.table_a(i INT);
> CREATE INDEX ON schema_a.table_a(function_x(i));
> INSERT INTO schema_a.table_a VALUES(1),(9),(2);

Mph. Well, actually what you've got there is a function that will break
anytime somebody looks at it sideways, anyhow. You need to
schema-qualify the reference to function_y, or if you don't want to do
that for some reason, you could attach a "SET search_path" clause to the
definition of function_x.

I don't regard this as a Postgres bug, because index functions are
required to be immutable, and function_x fails that test because its
results vary depending on search_path.

regards, tom lane