BUG #8698: cast and view

From: balazs(at)obiserver(dot)hu
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8698: cast and view
Date: 2013-12-23 18:43:37
Message-ID: E1VvATZ-0004mC-B4@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alvaro Herrera 2013-12-23 19:53:00 Re: BUG #8470: 9.3 locking/subtransaction performance regression
Previous Message Brett Neumeier 2013-12-23 17:45:35 Re: BUG #8684: Tables with custom range domain type cannot be analyzed