Обсуждение: pg_dump not including custom CAST based on table types

Hello,
I created a custom CAST to cast from one table type to another.
pg_dump does not include my custom CAST.

Here is an example:

CREATE TABLE foo_source(id integer);
CREATE TABLE foo_target(id integer);

CREATE OR REPLACE FUNCTION cast_ident(foo_source)
RETURNS foo_target
AS
$BODY$
   DECLARE
     result foo_target;
   BEGIN
     result.id=$1.id;
     RETURN result;
   END
$BODY$
LANGUAGE PLPGSQL VOLATILE;

CREATE CAST (foo_source AS foo_target)
    WITH FUNCTION cast_ident(foo_source)
    AS assignment;

--Casting works fine
SELECT (row(1)::foo_source)::foo_target as result;

--I can find the cast description in the catalog system.
SELECT castfunc::regprocedure,castsource::regtype,casttarget::regtype
FROM pg_cast
WHERE castsource='foo_source'::regtype and casttarget='foo_target'::regtype;



pg_dump -s -U postgres test > test.sql

when I look at the "test.sql" dumped file, I cannot find the CAST command.


I read carrefully the archives regarding my problem.

http://archives.postgresql.org/pgsql-general/2007-11/msg00931.php

Michael Glaesemann <grzm(at)seespotcode(dot)net> writes:
> On Nov 17, 2007, at 0:36 , Tom Lane wrote:
>> pg_dump thinks it's a built-in system object.

> What other objects might be susceptible to this? Operators? Operator
> classes?

It's just casts.  They're a bit of a problem since they have neither
owners nor schemas, so there's not anything very concrete to base a
dump-or-don't-dump decision on.  The rule pg_dump uses is to dump it
if at least one of the three underlying objects (source type, dest type,
or function) is dumpable.  Here you've got 2 builtin types and
no function, so you lose.

            regards, tom lane


My underlying objects are two tables foo_source and foo_target that can
be assimilated to types and they are dumpable.

Is there another rule or is it a bug?


Frédéric Rejol.

=?ISO-8859-1?Q?Fr=E9d=E9ric_Rejol?= <frederic.rejol@sescoi.fr> writes:
> I created a custom CAST to cast from one table type to another.
> pg_dump does not include my custom CAST.

Hmm.  The reason for that is that the table types aren't considered
dumpable objects.  I suppose we need to fix that, but in the meantime
you'd have better luck if you created the types as composite types
instead of implicit table rowtypes.

            regards, tom lane

On 18 Oct 2011, at 20:17, Tom Lane wrote:

> =?ISO-8859-1?Q?Fr=E9d=E9ric_Rejol?= <frederic.rejol@sescoi.fr> writes:
>> I created a custom CAST to cast from one table type to another.
>> pg_dump does not include my custom CAST.
>
> Hmm.  The reason for that is that the table types aren't considered
> dumpable objects.  I suppose we need to fix that, but in the meantime
> you'd have better luck if you created the types as composite types
> instead of implicit table rowtypes.


Maybe worth adding to the TODO.
Casts do exist in the database persistently, don't they ?

In which case it is only fair to have them in pg_dump, me thinks.

Greg Jaskiewicz <gryzman@gmail.com> writes:
> On 18 Oct 2011, at 20:17, Tom Lane wrote:
>> Hmm.  The reason for that is that the table types aren't considered
>> dumpable objects.  I suppose we need to fix that, but in the meantime
>> you'd have better luck if you created the types as composite types
>> instead of implicit table rowtypes.

> Maybe worth adding to the TODO.

If I hadn't done it yesterday, maybe so.

            regards, tom lane