Re: Problem with BETWEEN and a view.

> Looks like a bug to me, but I'd like not to have to reverse-engineer the
> test case before I can look at it. Could you provide some sample data,
> as well as the missing "Types" table declaration? Ideally a psql script
> file to load everything up from scratch and trigger the error ;-)

Well, I dont blame you!! I am going insane with this database over here...
arguably the 'bug' is that I'm doing something which shouldn't be done by a
normal sane person ;-)

Here's a simpler script which reproduces the bug:

CREATE TABLE Happy (
x int4 PRIMARY KEY,
y text
);
CREATE FUNCTION get_happyx(text) RETURNS int4 AS
'SELECT x FROM Happy WHERE y = $1' LANGUAGE 'sql' WITH (iscachable);

CREATE TABLE Joy (
happyx int4 REFERENCES Happy,
z text
);

INSERT INTO Happy (x,y) VALUES (1, 'One');
INSERT INTO Happy (x,y) VALUES (2, 'Two');
INSERT INTO Happy (x,y) VALUES (3, 'Three');
INSERT INTO Happy (x,y) VALUES (4, 'Four');

INSERT INTO Joy (happyx,z) VALUES (1, 'i love postgresql');
INSERT INTO Joy (happyx,z) VALUES (2, CURRENT_TIMESTAMP - '5 days'::interval);
INSERT INTO Joy (happyx,z) VALUES (3, CURRENT_TIMESTAMP + '5 days'::interval);
INSERT INTO Joy (happyx,z) VALUES (4, 'Tom Lane r0x0rs');

-- This view is the centre of the problem:

CREATE VIEW Depressed AS
SELECT j1.z AS Text1, j2.z::timestamp AS Start,
j3.z::timestamp AS Stop, j4.z AS Text2
FROM Joy j1, Joy j2, Joy j3, Joy j4
WHERE j1.happyx = get_happyx('One')
AND j2.happyx = get_happyx('Two')
AND j3.happyx = get_happyx('Three')
AND j4.happyx = get_happyx('Four');

----------------------------------------------------------------------------------------

Now to test it:

foo=# SELECT * FROM Depressed;
text1 | start | stop | text2
-------------------+------------------------+------------------------+-----------------
i love postgresql | 2000-11-10 17:25:45+11 | 2000-11-20 17:25:45+11 | Tom Lane r0x0rs
(1 row)

foo=# SELECT * FROM Depressed WHERE Start < CURRENT_TIMESTAMP AND Stop > CURRENT_TIMESTAMP;
ERROR: Bad timestamp external representation 'i love postgresql'

Bingo!

Hope that helps,
Andrew.