Quoth t...@sss.pgh.pa.us (Tom Lane):
> Ben Morrow writes:
> > I am trying to implement a fairly standard 'audit table' setup, but
> > using rules instead of triggers (since it should be more efficient).
>
> Rules are sufficiently tricky that I would never, ever rely on them for
> auditing. Use a
In response to Tom Lane :
> Ben Morrow writes:
> > I am trying to implement a fairly standard 'audit table' setup, but
> > using rules instead of triggers (since it should be more efficient).
>
> Rules are sufficiently tricky that I would never, ever rely on them for
> auditing. Use a simple AFT
Ben Morrow writes:
> I am trying to implement a fairly standard 'audit table' setup, but
> using rules instead of triggers (since it should be more efficient).
Rules are sufficiently tricky that I would never, ever rely on them for
auditing. Use a simple AFTER trigger instead.
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
However, I'm running into problems when one of the audited tables has a
'serial' column that is allowed to default:
create table foo (id serial, bar text);
Adrian Klaver wrote:
> On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote:
>> Hello list,
>>
>> I am trying to wirte a rule which calls a PLPgSQL-function upon an
>> Insert in a table. Here is a somewhat simplified example of what i got
>> so far:
>>
>> CREATE TABLE mytable (
>> mytab
On Thursday 17 September 2009 8:35:52 am Nico Mandery wrote:
> Hello list,
>
> I am trying to wirte a rule which calls a PLPgSQL-function upon an
> Insert in a table. Here is a somewhat simplified example of what i got
> so far:
>
> CREATE TABLE mytable (
> mytable_id serial PRIMARY KEY,
>
Hello list,
I am trying to wirte a rule which calls a PLPgSQL-function upon an
Insert in a table. Here is a somewhat simplified example of what i got
so far:
CREATE TABLE mytable (
mytable_id serial PRIMARY KEY,
something text
);
CREATE OR REPLACE FUNCTION _rule_insert_my(someth
Ray Madigan <[EMAIL PROTECTED]> writes:
> Does anyone have any suggestion?
Use a trigger to propagate the data to the other table. You can't make
this work reliably with a rule, because rules are macros and hence
inherently subject to double-evaluation problems when dealing with
volatile function
I have the following situation that I would appreciate your input on:
I have a table with a column that I use to uniquely identify its rows.
The table also has a rule on insert that stores the row identifier into
another table for reference at some other point.
The table is defined as
CREATE
On 10/6/05 9:07 PM, "Bath, David" <[EMAIL PROTECTED]> wrote:
> Folks,
>
> I'm looking at using pg to be the main platform for integrating info
> from other RDBMS products (particularly Oracle) as pg seems to be
> the most flexible RDBMS around.
>
> Disregarding writing to foreign products, query
Bath, David wrote:
Folks,
I'm looking at using pg to be the main platform for integrating info
from other RDBMS products (particularly Oracle) as pg seems to be
the most flexible RDBMS around.
Disregarding writing to foreign products, query-plan efficiencies,
or differences of SQL dialect, I'd
Folks,
I'm looking at using pg to be the main platform for integrating info
from other RDBMS products (particularly Oracle) as pg seems to be
the most flexible RDBMS around.
Disregarding writing to foreign products, query-plan efficiencies,
or differences of SQL dialect, I'd like to have a way of
Anslag
"Stephan Szabo" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> On Wed, 19 May 2004, Jie Liang wrote:
>
> > According to the document of rule:
> >
> > CREATE RULE rulename AS ON delete TO mytablename DO
> > (
> > delete from aaa where id=OLD.id;
> > Delete from bbb where id=OL
On Wed, 19 May 2004, Jie Liang wrote:
> According to the document of rule:
>
> CREATE RULE rulename AS ON delete TO mytablename DO
> (
> delete from aaa where id=OLD.id;
> Delete from bbb where id=OLD.id;
> Delete from ccc where id=OLD.id
> );
>
>
> Should work, but it doesn't, what wrong with it?
According to the document of rule:
CREATE RULE rulename AS ON delete TO mytablename DO
(
delete from aaa where id=OLD.id;
Delete from bbb where id=OLD.id;
Delete from ccc where id=OLD.id
);
Should work, but it doesn't, what wrong with it?
Even I use { }
Jie Liang
-
> tables but it introduces too much overhead. In any case, we need the return
> value (or an exception) from the function but there does not seem a way to
> return it.
Can't you use erreport() to return an exception message?
---(end of broadcast)-
We have an interesting case where we want to use Postgres both as a database
and a front end to a proprietary database. For the latter, we wrote
functions that access the proprietary database. Then we defined views on the
proprietary database and wrote rules for insert/update/delete on those views
Gunter Diehl <[EMAIL PROTECTED]> writes:
> create funtion f1(v, v) returns void as '...'
> create rule vupt as on update to v do instead select f1(new, old)
> While accepting this definitions, pg says it can not handle "whole-tuple
> references" at runtime.
FWIW, the "old" case works fine. "new"
How to pass the special variables new / old to functions?
e.g. I'd like to to something like this (v is a view):
create funtion f1(v, v) returns void as '...'
create rule vupt as on update to v do instead select f1(new, old)
While accepting this definitions, pg says it can not handle "whole-tupl
Ian,
> Thanks! I would have, but my messages bounce from SQL, even though I am
subscribed ( I get the messages, for crying out loud!)
send an e-mail to [EMAIL PROTECTED]
>
> PS RECORD doesn't work but I think that is because I am on 7.2.1
OK. I may have tested that on 7.4 devel by acci
Ian,
You're welcome. Replying to the list for the edification of other users.
-Josh
> I just figgered it out. I declared the function as
>
> create function some_func(test) returns int ...
>
> where test is the name of a table. The values are passed as a tcl array. I
will see if RECORD w
Ian,
> Anyway, I have a similar requirement, to intercept insert/update/delete and
redirect the data if a condition is met. Right now I am trying an INSTEAD
rule that puts the condtion in the WHERE of the rule definition. It seems to
work OK, but if the condition has exeptions where I would
Josh,
Thanks for the info.
I need to change an insert into an update when the key already exists.
I have been using a rules to test it on a small set (table) and it
works.
"Rules can't use indexes" just scared me. I will have to test on a
larger set.
Also, I had the impression that if a trigger
Bruce Momjian wrote:
> > > > 3) There are no AFTER Rules, making, for example, a rule with a table
> > check on
> > > > the new data impractical, so you'd want to use Triggers or Constraints
> > >
> > > We have changed ordering in 7.3 where I think INSERT rules are _after_
> > > the insert.
> >
Josh Berkus wrote:
>
> Bruce,
>
> > > 1) Rules can't use indexes to do their processing, so Rules which query
> large
> > > secondary tables can be a bad idea (maybe this has changed?)
> >
> > I don't think this is true. Rewrite is before optimizer so it should be
> > optimized just the same.
Bruce,
> > 1) Rules can't use indexes to do their processing, so Rules which query
large
> > secondary tables can be a bad idea (maybe this has changed?)
>
> I don't think this is true. Rewrite is before optimizer so it should be
> optimized just the same.
I was speaking if the Rule has to d
Josh Berkus wrote:
>
> Bruce, Richard,
>
> > Triggers are mostly for testing/modifying the row being
> > inserted/updated, while rules are better for affecting other rows or
> > other tables.
>
> Hmmm. Thought that there were also some other criteria:
>
> 1) Rules can't use indexes to do their
Bruce, Richard,
> Triggers are mostly for testing/modifying the row being
> inserted/updated, while rules are better for affecting other rows or
> other tables.
Hmmm. Thought that there were also some other criteria:
1) Rules can't use indexes to do their processing, so Rules which query large
No, the rule will affect all the rows using one query.
---
Jean-Luc Lachance wrote:
> Thanks for the info.
>
> Do you mean that if an update affects more than one row I should use
> triggers because the rules will be execut
Thanks for the info.
Do you mean that if an update affects more than one row I should use
triggers because the rules will be executed only once?
JLL
Richard Huxton wrote:
>
> On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote:
> > Hi all!
> >
> > Is there a guideline on the use of rules co
My book has a section on that:
http://www.postgresql.org/docs/awbook.html
Triggers are mostly for testing/modifying the row being
inserted/updated, while rules are better for affecting other rows or
other tables.
Jean-Luc Lachance wrote:
> Hi all!
>
> Is there a guideline on the use of
On Friday 06 Dec 2002 4:03 pm, Jean-Luc Lachance wrote:
> Hi all!
>
> Is there a guideline on the use of rules compared to triggers when both
> can be use to achieve the same result?
If I can use rules I do. Rules rewrite the query so are processed once,
whereas triggers get processed for every r
Hi all!
Is there a guideline on the use of rules compared to triggers when both
can be use to achieve the same result?
JLL
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Jerome Chochon wrote:
> Thanks for your answer but i have another question.
> Which one is the faster ?
>
> If i write this trigger...
> CREATE TRIGGER trigger_name
> BEFORE DELETE
> ON table_name
> FOR EACH ROW EXECUTE PROCEDURE function();
>
> and this rule:
> CREATE RULE name_rule AS
> ON DE
On Tue, Sep 10, 2002 at 09:45:16PM +0200, Michael Paesold wrote:
>
> Adam Erickson wrote:
>
> > Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> > Triggers are carried out after the SQL is executed and the data is
> modified.
> > So, to answer your question, I think rules
Adam Erickson wrote:
> Correct me if I'm wrong, but rules constrain the SQL (ie. validation).
> Triggers are carried out after the SQL is executed and the data is
modified.
> So, to answer your question, I think rules come first. As to which order
> the rules / triggers are executed probably de
u put them
in when you created them but I pulled that straight out of my arse. :)
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Michael Paesold
> Sent: Tuesday, September 10, 2002 1:53 PM
> To: [EMAIL PROTECTED]
> Subject: [SQL] Rule
Hi all,
can you tell me in what order rules and triggers are executed?
First, what comes first, the rules, or the triggers?
And then, in what order are all the rules / triggers executed?
Regards,
Michael Paesold
---(end of broadcast)---
TIP 6: H
Ahti Legonkov <[EMAIL PROTECTED]> writes:
> CREATE TABLE reo (
>"REO_ID" int4 DEFAULT nextval('"REO_ID_seq"'::text) NOT NULL,
>"TYPE" varchar(64) NOT NULL,
>CONSTRAINT "REO_ID_reo_ukey" UNIQUE ("REO_ID"),
>CONSTRAINT reo_pkey PRIMARY KEY ("REO_ID")
> );
> CREATE TABLE lreo (
>
Ahti Legonkov wrote:
> Does anyone know why since postgres 7.2 the rules are executed *after*
> the insert?
Because people where still complaining that they changed to execute
*before* in v6.4.
Jan
--
#==#
# It's easier to
Hi,
I have the following things in my database:
CREATE SEQUENCE "REO_ID_seq" INCREMENT 1 MINVALUE 1 MAXVALUE 2147483647
CACHE 1;
CREATE TABLE reo (
"REO_ID" int4 DEFAULT nextval('"REO_ID_seq"'::text) NOT NULL,
"TYPE" varchar(64) NOT NULL,
CONSTRAINT "REO_ID_reo_ukey" UNIQUE ("REO_ID")
On Tue, May 22, 2001 at 10:44:06AM +0300, Mart?n Marqu?s wrote:
> On Mar 22 May 2001 20:19, J.Fernando Moyano wrote:
> > Deleting is broken if it deletes more than one row. The rule is executed
> > only one time each delete command, and not one time each deleted row.
> >
> > It's this OK ??? Am i
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Well ... i'm testing carefully the two rules ==>
CREATE RULE piezas_add AS
ON insert TO piezas
DO update materia_prima set usadas=(usadas+1)
where n_material=new.n_material;
CREATE RULE piezas_delete AS
ON delete
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I need something like this .
CREATE RULE piezas_add AS
ON insert TO piezas
DO update materia_prima set usadas=(usadas+1)
where n_material=new.n_material;
CREATE RULE piezas_delete AS
ON delete TO piezas
D
Martín Marqués wrote:
> Is it posible to make a rule execute more then one query?
>
> Something like:
>
> CREATE RULE rule_name AS ON INSERT TO table1
> DO INSTEAD
> INSERT INTO table2 VALUES
> (new.value1,new.value2)
> INSERT INTO table3 VALUES
> (x,y)
Yes:
CREATE RULE rule_name AS
Is it posible to make a rule execute more then one query?
Something like:
CREATE RULE rule_name AS ON INSERT TO table1
DO INSTEAD
INSERT INTO table2 VALUES
(new.value1,new.value2)
INSERT INTO table3 VALUES
(x,y)
If not, is there a way to do this? Triggers maybe?
Saludos... :-)
--
El mejor si
Brett Schwarz wrote:
> If I have a rule, is the rule inside a tranaction along with the table that
> it references. For example, if I have a rule that deletes an entry from
> table B, whenever an entry in table A gets deleted, then is the delete for
> table A and table B wrapped inside the same tr
If I have a rule, is the rule inside a tranaction along with the table that
it references. For example, if I have a rule that deletes an entry from
table B, whenever an entry in table A gets deleted, then is the delete for
table A and table B wrapped inside the same transaction?
Same question I g
Peeter Smitt wrote:
> Hi
>
> I'm trying to make updateable view useing rules.
>
> CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
> SELECT fun1(new);
>
> Thing is that backend gives this error.
> ERROR: parser: parse error at or near ")"
>
> What i'm doing wrong? Are there any other
Peeter Smitt writes:
> CREATE RULE update_rule AS ON UPDATE TO table DO INSTEAD
> SELECT fun1(new);
>
> Thing is that backend gives this error.
> ERROR: parser: parse error at or near ")"
Try double quoting "new", since it's a reserved word.
--
Peter Eisentraut [EMAIL PROTECTED]
Hi
I'm trying to make updateable view useing
rules.
CREATE RULE update_rule AS ON UPDATE TO table DO
INSTEAD SELECT fun1(new);
Thing is that backend gives this
error.
ERROR: parser: parse error at or near
")"
What i'm doing wrong? Are there any other ways to
make updatebale mul
Tom Lane wrote:
> Jan Wieck <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> Hm. Perhaps the "cannot update view" test is too strict --- it's not
> >> bright enough to realize that the two rules together cover all cases,
> >> so it complains that you *might* be trying to update the view. As
Hi,
I'd like my rule to change the row which is resulting form select.
I'd like to get a row conforming to t1 from t2.
CREATE TABLE t1 (id int, f1 int, f2 int, f3 text, f4 text);
CREATE TABLE t2 (id int, p1 int, p2 text);
CREATE RULE r1 AS ON SELECT TO t2 DO INSTEAD
SELECT id, p1 AS f1, NULL::
Jan Wieck <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Hm. Perhaps the "cannot update view" test is too strict --- it's not
>> bright enough to realize that the two rules together cover all cases,
>> so it complains that you *might* be trying to update the view. As the
>> code stands, you mu
Tom Lane wrote:
> "Joel Burton" <[EMAIL PROTECTED]> writes:
> > create rule dev_ins as on update to dev_col_comments where
> > old.description isnull do instead insert into pg_description ( objoid,
> > description) values (old.att_oid, new.description);
>
> > create rule dev_upd as on update to de
"Joel Burton" <[EMAIL PROTECTED]> writes:
> In any event, though, the rule above crashes my backend, as do
> simpler versions I wrote that try your CREATE RULE DO INSTEAD (
> INSERT; UPDATE; ) idea.
Ugh :-(
> What information can I provide to the list to troubleshoot this?
A gdb backtrace fro
On 29 Nov 2000, at 19:42, Tom Lane wrote:
> "Joel Burton" <[EMAIL PROTECTED]> writes:
> > create rule dev_ins as on update to dev_col_comments where
> > old.description isnull do instead insert into pg_description (
> > objoid, description) values (old.att_oid, new.description);
>
> > create ru
Mark Hollomon <[EMAIL PROTECTED]> writes:
> On Wednesday 29 November 2000 19:42, Tom Lane wrote:
>> Hm. Perhaps the "cannot update view" test is too strict --- it's not
>> bright enough to realize that the two rules together cover all cases,
>> so it complains that you *might* be trying to update
On Wednesday 29 November 2000 19:42, Tom Lane wrote:
>
> Hm. Perhaps the "cannot update view" test is too strict --- it's not
> bright enough to realize that the two rules together cover all cases,
> so it complains that you *might* be trying to update the view. As the
> code stands, you must pr
"Joel Burton" <[EMAIL PROTECTED]> writes:
> create rule dev_ins as on update to dev_col_comments where
> old.description isnull do instead insert into pg_description ( objoid,
> description) values (old.att_oid, new.description);
> create rule dev_upd as on update to dev_col_comments where
> o
Am I misunderstanding how to use rule w/conditionals, or is there a
bug in this?
--
I love to use Pgsql comments, but find the 'comment on field...'
language a bit of a pain for documenting a large database at the
last minute. So, I wrote a query that pulls together all the fields in a
datab
Tom Lane wrote:
>
> Queries added by non-INSTEAD rules are always performed before the
> initially-given query, so you're right, the rule will see the unmodified
> value.
>
> I'd suggest folding the log-entry-making into your trigger, actually.
> If you have a trigger anyway then the insert into
Mark Volpe <[EMAIL PROTECTED]> writes:
> When I try this out, however, the rule seems to use the original
> value, rather than the "corrected" value.
Queries added by non-INSTEAD rules are always performed before the
initially-given query, so you're right, the rule will see the unmodified
value.
The actual trigger function I'm working with is over two screens long and
rather expensive to be calling twice! Perhaps I need to add another trigger
that updates the log table with the correct values after the fact. Recursive
triggers, fun! Thanks for the help,
Mark
Ang Chin Han wrote:
>
> Eit
On Wed, Aug 09, 2000 at 12:04:13PM -0400, Mark Volpe wrote:
> I have a table with a trigger that can potentially modify a row before it gets
> inserted or updated:
[snip]
> I have another table that tracks changes in the first table with rules:
AFAIK, rules get rewritten first, before triggers
Hi again,
I have a table with a trigger that can potentially modify a row before it gets
inserted or updated:
CREATE TABLE t1 (a int);
CREATE FUNCTION t1_validate() RETURNS opaque AS
'
BEGIN
IF (NEW.a>10) THEN NEW.a=10; END IF;
Itai Zukerman <[EMAIL PROTECTED]> writes:
> CREATE RULE data_val AS
> ON SELECT TO data
> DO INSTEAD
> SELECT * FROM pos
> UNION
> SELECT * FROM neg;
IIRC, UNION doesn't work in rules at the moment (a fix is planned for
7.2). But what's the point of splitting the data into the
I'm doing this:
CREATE TABLE data ( val int4 );
CREATE TABLE pos ( ) INHERITS ( data );
CREATE TABLE neg ( ) INHERITS ( data );
CREATE RULE data_to_pos AS
ON INSERT TO data WHERE NEW.val > 0
DO INSTEAD
INSERT INTO pos ( val ) VALUES ( NEW.val );
CREATE RULE data_to_neg AS
ON
Hello all,
Forgive my ignorance. I'm trying to understand the RULES in PostgreSQL and
I have several questions. Why the rules if they are not part of SQL92 or
SQL3? Where do they come from? Are they from before the SQL history of
Postgres? Many things that can be done with RULES can be done with
69 matches
Mail list logo
