Thread: views and rules

views and rules

From

"Chad Thompson"

Date:

09 July 2002, 17:02:03

I have a 3rd party software that submits information to my database in a table called call_results. The problem is that it submits the date to one column(calldate) and the time to another (start_time). The reports that i need to run, go significantly faster if date and time are in one column.

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

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.

I have tried to work around this by creating a table called call_results with start_time being just a time field and creating similar rule on the table call_results. With this senario I have problems with the select do instead rule.

**whew!** Does that make sense?

Thanks in advance for reading my epic novel, and for any help you can give.

Chad

Re: views and rules

From

Masaru Sugawara

Date:

11 July 2002, 15:56:53

On Tue, 9 Jul 2002 12:01:56 -0600
"Chad Thompson" <chad@weblinkservices.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
wouldseem) 
>
> 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

Re: views and rules

From

"Chad Thompson"

Date:

11 July 2002, 16:56:47

Perfect! Thanks

Chad
----- Original Message -----
From: "Masaru Sugawara" <rk73@sea.plala.or.jp>
To: "Chad Thompson" <chad@weblinkservices.com>
Cc: <pgsql-novice@postgresql.org>
Sent: Thursday, July 11, 2002 10:57 AM
Subject: Re: [NOVICE] views and rules


> On Tue, 9 Jul 2002 12:01:56 -0600
> "Chad Thompson" <chad@weblinkservices.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
>
>
>