pg_dump: SQL command failed - Mailing list pgsql-bugs

From Thangalin
Subject pg_dump: SQL command failed
Date
Msg-id CAANrE7pEvWk9LBvEK8kHLOsMNiDGgumVB=gYRMojtdPc6UCO6Q@mail.gmail.com
Whole thread Raw
Responses Re: pg_dump: SQL command failed  (Thangalin <thangalin@gmail.com>)
Re: pg_dump: SQL command failed  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: pg_dump: SQL command failed  (Robert Haas <robertmhaas@gmail.com>)
List 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

pgsql-bugs by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: [SQL] pg_dump: aborting because of server version mismatch
Next
From: Thangalin
Date:
Subject: Re: pg_dump: SQL command failed