help with rules for system table

Does this mean that I can't create rules for system tables?

that's right... you cannot create rules for system tables...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

rlhsiao@gmail.com writes:

Does this mean that I can't create rules for system tables?

Not ones that will be invoked by system operations, anyway.

regards, tom lane

On Sun, Jan 08, 2006 at 06:35:06PM -0800, rlhsiao@gmail.com wrote:

Hi,

I have to archieve functions like this:
When users define a new index, I will do something (for instance,
increase an counter in my table or do some other statistics). However,
I defined rule for insert on pg_class and when the entry is actually an
index, I do my thing. Obviously my rule is never executed. If I create
a similar rule for a table I defined, it works. Does this mean that I
can't create rules for system tables? If not, does anyone know how to
do this?

Many system operations completely bypass the 'normal' access methods for
touching the system tables, so generally you can't do things like
triggers or rules.

Depending on what you need to do there may be other ways to accomplish
it, though. For example, it's trivial to get a count of indexes on a
table...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

Adding -general back into this...

No, I don't think there's any way to trigger on this programmatically, though there has been talk from time-to-time about adding support for triggers on DDL. In the meantime you'll just need to scan the system tables for new indexes.

I'm also wondering if there's some other, better way to do what you ultimately are trying to do. Adding a function based on CREATE INDEX seems extremely odd, at least to me.

From: zu zu [mailto:rlhsiao@gmail.com]
Hi Jim,

Thank you for the message.
Actually what I wanted to do is:
whenever a user defines a new index, I will automatically generate 2
functions (specific to that index) in the database.
Of course, I can achieve this by, for instance, check if there's new
index defined every night and if so, generate corresponding
functions. Since most of the time, my database won't have drastic
changes at all so this would be a waste of computational power.
Originally I thought rule and trigger are the perfect
solution for this.
Now it seems that it's not possible to achieve this.

Do you know other ways to achieve this?

Thanks
Ruey-Lung

Jim C. Nasby wrote:

On Sun, Jan 08, 2006 at 06:35:06PM -0800, rlhsiao@gmail.com wrote:

Hi,

I have to archieve functions like this:
When users define a new index, I will do something (for instance,
increase an counter in my table or do some other

statistics). However,

I defined rule for insert on pg_class and when the entry

is actually an

index, I do my thing. Obviously my rule is never

executed. If I create

a similar rule for a table I defined, it works. Does this

mean that I

can't create rules for system tables? If not, does anyone

know how to

do this?

Many system operations completely bypass the 'normal'

access methods for

touching the system tables, so generally you can't do things like
triggers or rules.

Depending on what you need to do there may be other ways to

accomplish

it, though. For example, it's trivial to get a count of indexes on a
table...

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461