Re: pg_dump: SQL command failed

Hi,

*WORKAROUND*

Until this is fixed, here is a workaround:

1. Comment out the following line:

SELECT unaccent($1);

2. Change the code to:

SELECT $1;

3. Run pg_dump as before.

4. Remember to reinstate the old code. ;-)

Works as expected.

Thank you!

Dave

On Sun, May 13, 2012 at 7:46 PM, Thangalin <thangalin(at)gmail(dot)com> wrote:

> 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
>