Re: [GENERAL] pg_dump not including custom CAST based on table types
On 18 Oct 2011, at 20:17, Tom Lane wrote: =?ISO-8859-1?Q?Fr=E9d=E9ric_Rejol?= frederic.re...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump not including custom CAST based on table types
Greg Jaskiewicz gryz...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump not including custom CAST based on table types
=?ISO-8859-1?Q?Fr=E9d=E9ric_Rejol?= frederic.re...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dump not including custom CAST?
On Nov 17, 2007, at 0:36 , Tom Lane wrote: D. Dante Lorenso [EMAIL PROTECTED] writes: I did this in my database: CREATE CAST (VARCHAR AS BYTEA) WITHOUT FUNCTION; I'm using PostgreSQL 8.2.4 for both the dump and restore database. Why doesn't the CAST dump and restore? pg_dump thinks it's a built-in system object. What other objects might be susceptible to this? Operators? Operator classes? Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pg_dump not including custom CAST?
Michael Glaesemann [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] pg_dump not including custom CAST?
All, I did this in my database: CREATE CAST (VARCHAR AS BYTEA) WITHOUT FUNCTION; But when I use pg_dump to dump the database and use pg_restore to bring it back on a freshly created database, the CAST is the only part of the restore which is missing. I'm using PostgreSQL 8.2.4 for both the dump and restore database. Why doesn't the CAST dump and restore? -- Dante ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_dump not including custom CAST?
D. Dante Lorenso [EMAIL PROTECTED] writes: I did this in my database: CREATE CAST (VARCHAR AS BYTEA) WITHOUT FUNCTION; I'm using PostgreSQL 8.2.4 for both the dump and restore database. Why doesn't the CAST dump and restore? pg_dump thinks it's a built-in system object. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match