Hi John et al,
Thanks John, I'd assumed they were effectively synonyms for byref and so
put them in the execute procedure statement and hadn't thought of
putting them in the create procedure...
It works like a dream now!
Marty
-----Original Message-----
From: info-ingres-bounces@[EMAIL PROTECTED]
On Behalf Of John
Smedley
Sent: 03 July 2008 15:02
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] subpartitions and date functions
And here is an example of BEFORE rule
Create table eyeballz(
my_key integer not null not default,
capture_date ingresdate not null not default,
capture_month smallint);
create procedure p_eyeballz (in p1 ingresdate, out p2 = smallint)
as
begin
select p2 = month(:p1);
end;
create rule r_eyeballz
before insert, update (capture_date) on eyeballz
for each row
execute procedure p_eyeballz (p1 = new.capture_date, p2 =
new.capture_month)
;
modify eyeballz to btree on capture_date with partition=((
range on capture_date
partition eyeballz_pre_2008 values < '1/1/2008',
partition eyeballz_2008 values < '1/1/2009',
partition eyeballz_post_2008 values >= '1/1/2009')
subpartition (list on capture_month
partition eyeballz_sp_jan values (1),
partition eyeballz_sp_feb values (2),
partition eyeballz_sp_mar values (3),
partition eyeballz_sp_apr values (4),
partition eyeballz_sp_may values (5),
partition eyeballz_sp_jun values (6),
partition eyeballz_sp_jul values (7),
partition eyeballz_sp_aug values (8),
partition eyeballz_sp_sep values (9),
partition eyeballz_sp_oct values (10),
partition eyeballz_sp_nov values (11),
partition eyeballz_sp_dec values (12, DEFAULT))
);
insert into eyeballz (my_key, capture_date) values
(random(1, 1000), date('today') + concat(ascii(random(1, 365)),
'
days'));
select * from eyeballz;
-----Original Message-----
From: info-ingres-bounces@[EMAIL PROTECTED]
On Behalf Of
Martin Bowes
Sent: 03 July 2008 14:54
To: Karl & Betty Schendel; Robert Kibble
Cc: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] subpartitions and date functions
Hi Guys,
1. 37, Yes it is 36 plus the base table. I suppose the base stays in
iirelations as a marker of some type.
2. modify eyeballz partition eyeballz_2008.eyeballz_sp_mar to
reconstruct; Works!
3. Yes the AFTER rule wouldn't be a good idea in this case, much better
to use a BEFORE rule. Now if only I can work out how to get it to alter
the value of the capture_month column in the row being inserted.
Marty
-----Original Message-----
From: Karl & Betty Schendel [mailto:schendel@[EMAIL PROTECTED]
Sent: 03 July 2008 14:18
To: Martin Bowes
Subject: Re: [Info-Ingres] subpartitions and date functions
On Jul 3, 2008, at 8:15 AM, Martin Bowes wrote:
> Hi Karl, Doug et al....
>
> So I changed my eyeballz to...
> modify eyeballz to btree on capture_date
> with partition=((
> range on capture_date
> partition eyeballz_pre_2008 values < '1/1/2008',
> partition eyeballz_2008 values < '1/1/2009',
> partition eyeballz_post_2008 values >= '1/1/2009')
> subpartition (list on capture_month
> partition eyeballz_sp_jan values (1),
> partition eyeballz_sp_feb values (2),
> partition eyeballz_sp_mar values (3),
> partition eyeballz_sp_apr values (4),
> partition eyeballz_sp_may values (5),
> partition eyeballz_sp_jun values (6),
> partition eyeballz_sp_jul values (7),
> partition eyeballz_sp_aug values (8),
> partition eyeballz_sp_sep values (9),
> partition eyeballz_sp_oct values (10),
> partition eyeballz_sp_nov values (11),
> partition eyeballz_sp_dec values (12, DEFAULT))
> );
>
> Questions:
> 1. Why did I end up with 37 partitions? I would expect 36. I
> checked all
> the partitions in
> iiextend all hang off the eyeballz table and no others.
Where did you get 37 from? There will be 37 iirelation entries,
one for the master and one for each of 36 physical partitions.
>
> 2. How do I refer to a specific subpartition in a modify statement...
> modify eyeballz partition eyeballz_2008 subpartition
> eyeballz_sp_mar to reconstruct;
modify eyeballz partition eyeballz_2008.eyeballz_sp_mar to reconstruct;
ought to work. The syntax is table partition pname.pname.... and
if any of the dimensions are omitted it's supposed to imply "all".
So: modify eyeballz partition eyeballz_2008 to ...
and: modify eyeballz partition eyeballz_sp_nov
to do all of 2008 and all novembers, respectively.
>
> BTW. I cant set the capture_month with an RFP as that would be
> triggered
> after the initial insert. I'll have to make a BEFORE trigger to do
> that
> job...which also means upgrading to 9.1.1...This just keeps getting
> better!
An after trigger ought to work, but it would probably move the row
across
partitions, which I agree would be undesirable.
Karl
_______________________________________________
Info-Ingres mailing list
Info-Ingres@[EMAIL PROTECTED]
mailing list
Info-Ingres@[EMAIL PROTECTED]
|
