Hi,
*REPLICATE*
0. Create a new database (*superdatabase*)
1. Create a new schema (*superschema*)
2. Add the unaccent extension to the schema:
CREATE EXTENSION unaccent;
3. Create a wrapper for unaccent that exposes an *IMMUTABLE* interface
(this is side issue):
CREATE OR REPLACE FUNCTION superschema.unaccent_text(text)
RETURNS text AS
$BODY$
-- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
SELECT unaccent($1);
$BODY$
LANGUAGE sql IMMUTABLE
COST 1;
4. Dump the schema using pg_dump:
pg_dump -n *superschema* --inserts *superdatabase* > superduper.sql
*EXPECTED RESULTS*
A file named superduper.sql with all the SQL statements needed to recreate
a fresh copy of *superschema* within *superdatabase* in another PostgreSQL
instance running on another server.
*ACTUAL RESULTS*
Failure:
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR: function unaccent(text) does
not exist
LINE 3: SELECT unaccent($1);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
QUERY:
-- unaccent is STABLE, but the indexes must use IMMUTABLE functions.
SELECT unaccent($1);
CONTEXT: SQL function "unaccent_text" during inlining
Shouldn't need to typecast, just dump the function's text to a file without
trying to inline it or otherwise interpret it. The function works when the
application is running, so it should also export using pg_dump without
quibbles.
For what it's worth, adding typecasts didn't help:
SELECT unaccent($1::text)::text;
Kind regards,
Dave