Re: BUG #8698: cast and view

The following bug has been logged on the website:

Bug reference: 8698
Logged by: Balazs Peter Odor
Email address: balazs(at)obiserver(dot)hu
PostgreSQL version: 9.3.2
Operating system: Debian Linux
Description:

CREATE SCHEMA test;
CREATE TYPE test.customcompositetype AS (
x text,
y text
);
CREATE TABLE test.customtable (
compositetype test.customcompositetype
);
INSERT INTO test.customtable (compositetype)
VALUES ('(x1,y1)'), ('(x2,y2)');

SELECT * FROM test.customtable;
compositetype
---------------
(x1,y1)
(x2,y2)
(2 rows)

CREATE VIEW test.customview as
SELECT compositetype, compositetype::text AS compositetype_text
FROM test.customtable;

SELECT * FROM test.customview;
compositetype | compositetype_text
---------------+--------------------
(x1,y1) | (x1,y1)
(x2,y2) | (x2,y2)
(2 rows)

Ok, lets create a cast to customcompositetype to text:

CREATE FUNCTION test.customcompositetype_text(test.customcompositetype)
RETURNS text AS
$$SELECT ($1).x || ' ' || ($1).y;$$
IMMUTABLE
LANGUAGE SQL
;
CREATE CAST (test.customcompositetype AS text)
WITH FUNCTION test.customcompositetype_text(test.customcompositetype);

SELECT * FROM test.customview;
compositetype | compositetype_text
---------------+--------------------
(x1,y1) | (x1,y1)
(x2,y2) | (x2,y2)
(2 rows)

What?? :(

Refresh the view:

CREATE OR REPLACE VIEW test.customview as
SELECT compositetype, compositetype::text AS compositetype_text
FROM test.customtable;

SELECT * FROM test.customview;
compositetype | compositetype_text
---------------+--------------------
(x1,y1) | x1 y1
(x2,y2) | x2 y2
(2 rows)

Whats wrong? Why need recreate the view??

balazs(at)obiserver(dot)hu writes:
> Whats wrong? Why need recreate the view??

Because it contains a call to the original cast function.

regards, tom lane

Hi,

Please create a full pg_dump before recreate the view, drop the
database, and recreate it from the full dump.

This procedure (dump and restore) isn't represent the original state!

Regards,
Balazs