Обсуждение: Is it possible to redirect an update/insert/delete to a different table?

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Is there a way that I can specify that a request to change to a particular table is redirected
insteadto a different table?</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I’ve looked through rules, and as far as I can make out, they are only useful for explicit actions.
I’mlooking for something that behaves as though it simply substitutes the table name for a different table name before
executingthe command, no matter what the command looks like.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Andy Ballingall</span></font></div>

Andy Ballingall wrote:
> I've looked through rules, and as far as I can make out, they are
> only useful for explicit actions. I'm looking for something that
> behaves as though it simply substitutes the table name for a
> different table name before executing the command, no matter what the
> command looks like.

You can write a couple of rules for that.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

> I've looked through rules, and as far as I can make out, they are only
> useful for explicit actions. I'm looking for something that behaves as
> though it simply substitutes the table name for a different table name
> before executing the command, no matter what the command looks like.
>

Make 3 rules (INSERT/UPDATE/DELETE), 4 if you want SELECT as well...

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

Hello Peter,

I'm glad it's possible, but I can't see how from the documentation.

Say if I have a table called 'apples' and a table called 'pears'.

What would the rule look like that would remap all updates on apples so that
they were applied to pears instead?

Thanks,
Andy

-------------------------

Andy Ballingall wrote:
> I've looked through rules, and as far as I can make out, they are
> only useful for explicit actions. I'm looking for something that
> behaves as though it simply substitutes the table name for a
> different table name before executing the command, no matter what the
> command looks like.

You can write a couple of rules for that.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/



-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005

Hello Jaime,

I'm still not quite clear.

Say I have a number of different updates on a table 'apples' in my code,
including:

UPDATE apples set pips=6 and color='yellow' where id=3;
UPDATE apples set size=10 where id=6;

What would a rule look like which, when *any* update is attempted on the
apples table, will instead apply the update to a different table - 'pears'.

I get this far:

CREATE rule pears_instead_of_apples AS ON UPDATE TO applesDO INSTEAD UPDATE INTO pears .....;

What do I put where the ..... is, so that the rule will transparently update
the pears table with whatever values happened to be defined by the original
update command? Is there a special keyword that I've missed?

Regards,
Andy Ballingall


-----Original Message-----
From: Jaime Casanova [mailto:systemguards@gmail.com] 
Sent: 20 November 2005 14:23
To: andy@areyoulocal.co.uk
Cc: pgsql-sql@postgresql.org
Subject: Re: Is it possible to redirect an update/insert/delete to a
different table?

> I've looked through rules, and as far as I can make out, they are only
> useful for explicit actions. I'm looking for something that behaves as
> though it simply substitutes the table name for a different table name
> before executing the command, no matter what the command looks like.
>

Make 3 rules (INSERT/UPDATE/DELETE), 4 if you want SELECT as well...

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



-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005

Andy Ballingall <andy@areyoulocal.co.uk> schrieb:

> Hello Peter,
> 
> I'm glad it's possible, but I can't see how from the documentation.
> 
> Say if I have a table called 'apples' and a table called 'pears'.
> 
> What would the rule look like that would remap all updates on apples so that
> they were applied to pears instead?

create rule apples_pears_update as on update to apples do instead update pears set name= NEW.name where id=NEW.id ;

test=# select * from apples ;id | name
----+------ 1 | a
(1 row)

test=# select * from pears ;id | name
----+------ 1 | b
(1 row)

test=# update apples set name = 'c' where id = 1;
UPDATE 1
test=# select * from pears ;id | name
----+------ 1 | c
(1 row)


http://www.postgresql.org/docs/8.1/interactive/rules-update.html

HTH, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Hi Andreas,

The rule you've given only works for an update which changes the name.

If I do another update which changed the colour instead of the name, that
rule wouldn't do the right thing.

Instead, I'm looking for something which, with a single 'rule' (or whatever
the mechanism ends up being), intercepts *any* update on apples, and applies
the changes to the pears table instead, as if the only change that occurred
was a change of table name.

I can achieve this in the application which generates the sql commands, but
someone else suggested it was possible with rules, but it may not be the
case.

Thanks!
Andy




-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]
On Behalf Of Andreas Kretschmer
Sent: 20 November 2005 16:17
To: pgsql-sql@postgresql.org
Subject: Re: [SQL] Is it possible to redirect an update/insert/delete to a
different table?

Andy Ballingall <andy@areyoulocal.co.uk> schrieb:

> Hello Peter,
>
> I'm glad it's possible, but I can't see how from the documentation.
>
> Say if I have a table called 'apples' and a table called 'pears'.
>
> What would the rule look like that would remap all updates on apples so
that
> they were applied to pears instead?

create rule apples_pears_update as on update to apples do instead update
pears set name= NEW.name where id=NEW.id ;

test=# select * from apples ;id | name
----+------ 1 | a
(1 row)

test=# select * from pears ;id | name
----+------ 1 | b
(1 row)

test=# update apples set name = 'c' where id = 1;
UPDATE 1
test=# select * from pears ;id | name
----+------ 1 | c
(1 row)


http://www.postgresql.org/docs/8.1/interactive/rules-update.html

HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.4/176 - Release Date: 20/11/2005

Andy Ballingall <andy@areyoulocal.co.uk> schrieb:

> Hello Jaime,
> 
> I'm still not quite clear.
> 
> Say I have a number of different updates on a table 'apples' in my code,
> including:
> 
> UPDATE apples set pips=6 and color='yellow' where id=3;
> UPDATE apples set size=10 where id=6;
> 
> What would a rule look like which, when *any* update is attempted on the
> apples table, will instead apply the update to a different table - 'pears'.

Try it.

test=# create table apples (id int, name1 text, name2 text);
CREATE TABLE
test=# create table pears (id int, name1 text, name2 text);
CREATE TABLE
test=# create rule apples_pears_update as on update to apples do instead
update pears set name1= NEW.name1, name2=NEW.name2 where id=NEW.id ;
CREATE RULE
test=# insert into apples values (1, 'a', 'a');
INSERT 0 1
test=# insert into pears values (1, 'a', 'a');
INSERT 0 1
test=#
test=# update apples set name1='b' where id = 1;
UPDATE 1
test=# select * from pears ;id | name1 | name2
----+-------+------- 1 | b     | a
(1 row)

test=# update apples set name2='c' where id = 1;
UPDATE 1
test=# select * from pears ;id | name1 | name2
----+-------+------- 1 | a     | c
(1 row)

test=# update apples set name1='e', name2='e' where id = 1;
UPDATE 1
test=# select * from pears ;id | name1 | name2
----+-------+------- 1 | e     | e
(1 row)



> 
> -----Original Message-----

Please, no top-posting.


HTH, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Andy Ballingall <andy@areyoulocal.co.uk> schrieb:

> Hi Andreas,
> 
> The rule you've given only works for an update which changes the name.
> 
> If I do another update which changed the colour instead of the name, that
> rule wouldn't do the right thing.

Right.


> Instead, I'm looking for something which, with a single 'rule' (or whatever
> the mechanism ends up being), intercepts *any* update on apples, and applies
> the changes to the pears table instead, as if the only change that occurred
> was a change of table name.

My example was simple, right.


> I can achieve this in the application which generates the sql commands, but
> someone else suggested it was possible with rules, but it may not be the
> case.

Inside the rule you have the NEW-Variable with all values to update.
Read my other answer.
I'm not sure, perhaps it is possible to write a more generic rule.

> 
> -----Original Message-----

Please, no top-posting with silly fullquote below. 


HTH, Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

>Try it. [snipped example]

Ah. Basically, you set up the rule to assign every column, and if the update
doesn't redefine some columns, then it still works. I didn't understand that
you could get the rule to work generically like this.

I'll presume that the rule will need amending if the table column definition
later changes. (E.g. if I add 'stalk_length' to my apples and pears
tables)...

Thanks very much for your help.

Andy

On Sunday 20 November 2005 09:15 am, Andy Ballingall wrote:
It works because of the way updates are done. When you do an update two 
versions of the row exist. The OLD version is the row as it existed before 
you updated. The NEW version contains the entire version with the update 
changes. The key thing to remember is the the NEW version contains both those 
fields that have changed as well as those that have not. So the UPDATE rule 
just passes along all the fields named in it regardless of whether they 
changed or not. It would be a good idea to read the following section of the 
manual (http://www.postgresql.org/docs/8.0/interactive/rules.html) as it 
explains when the rule picks up the values in the fields. This differs 
according to the type of rule.
> >Try it. [snipped example]
>
> Ah. Basically, you set up the rule to assign every column, and if the
> update doesn't redefine some columns, then it still works. I didn't
> understand that you could get the rule to work generically like this.
>
> I'll presume that the rule will need amending if the table column
> definition later changes. (E.g. if I add 'stalk_length' to my apples and
> pears tables)...
>
> Thanks very much for your help.
>
> Andy
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

-- 
Adrian Klaver    
aklaver@comcast.net

I don't know what do you mean... because if you want to insert to other
table.. you can create a rule like this

CREATE RULE myrule AS ON INSERT
TO mytable
DO INSTEAD INSERT INTO myothertable VALUES (NEW.col1, NEW.col2,
NEW.col3);

that will work for all inserts.. I think the problem will be the
SELECT, right?... well.. that's solved if "mytable" is a view of SELECT
* FROM myothertable..

Juan C. Olivares
www.juancri.com