Re: factoring problem with view in 7.3.3 [ SOLVED ]

Yep!

it works perfectly now.

CREATE or replace VIEW sent_enquiry_eyp_iid_ip_cat2 as ((((((SELECT
eyp_rfi.rfi_id, eyp_rfi.sender_uid, eyp_rfi.receiver_uid, eyp_rfi.subject,
cast(eyp_rfi.generated as timestamp with time zone ) FROM ONLY eyp_rfi)
UNION (SELECT iid_rfi.rfi_id, iid_rfi.sender_uid, iid_rfi.receiver_uid,
iid_rfi.subject, iid_rfi.generated FROM ONLY iid_rfi))) UNION (SELECT
ip_rfi.rfi_id, ip_rfi.sender_uid, ip_rfi.receiver_uid, ip_rfi.subject,
cast(ip_rfi.generated as timestamp with time zone ) FROM ONLY ip_rfi)))
UNION (SELECT catalog_rfi.rfi_id, catalog_rfi.sender_uid,
catalog_rfi.receiver_uid, catalog_rfi.subject, catalog_rfi.generated FROM
ONLY catalog_rfi));
tradein_clients=# explain analyze SELECT rfi_id from
sent_enquiry_eyp_iid_ip_cat2 where sender_uid=38466; QUERY
PLAN------------------------------------------------------------------------------------------------------------------------------------------------------------------ Subquery Scan sent_enquiry_eyp_iid_ip_cat2 (cost=641.62..644.67 rows=20
width=55) (actual time=0.17..0.17 rows=0 loops=1) -> Unique (cost=641.62..644.67 rows=20 width=55) (actual
time=0.17..0.17 rows=0 loops=1) -> Sort (cost=641.62..642.12 rows=204 width=55) (actual
time=0.17..0.17 rows=0 loops=1) Sort Key: rfi_id, sender_uid, receiver_uid, subject, generated
-> Append (cost=0.00..633.80 rows=204 width=55) (actual
time=0.08..0.08 rows=0 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..376.11
rows=117 width=42) (actual time=0.03..0.03 rows=0
loops=1) -> Index Scan using eyp_sender_uid_idx on
eyp_rfi (cost=0.00..376.11 rows=117 width=42)
(actual time=0.03..0.03 rows=0 loops=1) Index Cond: (sender_uid = 38466)
-> Subquery Scan "*SELECT* 2" (cost=0.00..81.33
rows=21 width=47) (actual time=0.02..0.02 rows=0
loops=1) -> Index Scan using iid_sender_uid_idx on
iid_rfi (cost=0.00..81.33 rows=21 width=47)
(actual time=0.02..0.02 rows=0 loops=1) Index Cond: (sender_uid = 38466)
-> Subquery Scan "*SELECT* 3" (cost=0.00..160.18
rows=57 width=42) (actual time=0.02..0.02 rows=0
loops=1) -> Index Scan using ip_sender_uid_idx on
ip_rfi (cost=0.00..160.18 rows=57 width=42)
(actual time=0.02..0.02 rows=0 loops=1) Index Cond: (sender_uid = 38466)
-> Subquery Scan "*SELECT* 4" (cost=0.00..16.19
rows=8 width=55) (actual time=0.01..0.01 rows=0
loops=1) -> Index Scan using catalog_sender_uid_idx on
catalog_rfi (cost=0.00..16.19 rows=8 width=55)
(actual time=0.01..0.01 rows=0 loops=1) Index Cond: (sender_uid = 38466)
Total runtime: 0.41 msec
(18 rows)

regds
mallah.

> <mallah(at)trade-india(dot)com> writes:
>> the column 'generated' was timestamp in 2 place and date in 2 place, i
>> wanted it in my and did a typecasting in the view below
>> but it suffers from the same problem .
>
> AFAIR it should work if you insert casts into the UNION's member
> selects. Maybe you didn't get the casting quite right? (For instance,
> "timestamp" isn't "timestamp with time zone" ...)
>
> regards, tom lane
>
> ---------------------------(end of
> broadcast)--------------------------- TIP 6: Have you searched our list
> archives?
>
> http://archives.postgresql.org

-----------------------------------------
Over 1,00,000 exporters are waiting for your order! Click below to get
in touch with leading Indian exporters listed in the premier
trade directory Exporters Yellow Pages.
http://www.trade-india.com/dyn/gdh/eyp/