From: | Masaru Sugawara <rk73(at)sea(dot)plala(dot)or(dot)jp> |
---|---|
To: | "Chad Thompson" <chad(at)weblinkservices(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: views and rules |
Date: | 2002-07-11 16:57:33 |
Message-ID: | 20020712015651.5490.RK73@sea.plala.or.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tue, 9 Jul 2002 12:01:56 -0600
"Chad Thompson" <chad(at)weblinkservices(dot)com> wrote:
> --this is what he submits to me
> insert into call_results (phonenum, callnum, calldate, start_time)
> values ('5552552555', 4524, '6/24/2002', '17:00:32')
>
> To get around this i have tried to create the following.
>
> CREATE TABLE "call_results_fixed" (
> "id" int8 DEFAULT nextval('"call_results_fixed_id_seq"'::text) NOT NULL,
> "callnum" int4,
> "calldate" timestamp,
> "phonenum" varchar(15),
> "start_time" timestamp
> CONSTRAINT "call_results_fixed_pkey" PRIMARY KEY ("id")
> ) WITH OIDS;
>
> CREATE VIEW "call_results" AS SELECT * from call_results_fixed
It appears that the types of the columns defined as a view are different
from those of the values inserted into it. I think it is probably a useful way
to explicitly cast like the following.
CREATE VIEW call_results AS
SELECT phonenum, callnum, calldate, start_time::text
FROM call_results_fixed;
>
> CREATE RULE ins_call_results AS ON INSERT TO call_results
> DO INSTEAD INSERT INTO call_results_fixed (calldate, callnum, phonenum, start_time)
> VALUES ("timestamp"(new.calldate), new.callnum, new.phonenum, "timestamp"(((to_char(timestamptz(new.calldate), 'MM-DD-YYYY'::text) || ' '::text) || text(new.start_time))));
>
> This would effectivly concatinate the date and time and insert them into the call_results_fixed table. (or so it would seem)
>
> I get an error when i set this up, however that says
>
> "Bad timestamp external representation '17:00:32'
>
> In other words, it seems that it is testing the column type without taking into account the rule.
>
Regards,
Masaru Sugawara
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Price | 2002-07-11 17:52:42 | Re: web archiving |
Previous Message | Tom Lane | 2002-07-11 14:37:45 | Re: Getting result set metadata without executing query? |