pg_dump: SQL command failed

From: Thangalin <thangalin(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_dump: SQL command failed
Date: 2012-05-14 02:46:12
Message-ID: CAANrE7pEvWk9LBvEK8kHLOsMNiDGgumVB=gYRMojtdPc6UCO6Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Thangalin 2012-05-14 02:52:24 Re: pg_dump: SQL command failed
Previous Message Adrian Klaver 2012-05-11 19:39:18 Re: [SQL] pg_dump: aborting because of server version mismatch