From andy@areyoulocal.co.uk Sun Nov  20 00:26:58 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id EBB05DBC68
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sat, 19 Nov 2005 20:26:58 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 34865-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 00:27:00 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp-out6.blueyonder.co.uk (smtp-out6.blueyonder.co.uk
	[195.188.213.9])
	by svr1.postgresql.org (Postfix) with ESMTP id B4AFCDBC50
	for <pgsql-sql@postgresql.org>; Sat, 19 Nov 2005 20:26:54 -0400 (AST)
Received: from Eve ([82.43.185.102]) by smtp-out6.blueyonder.co.uk with
	Microsoft SMTPSVC(5.0.2195.6713); Sun, 20 Nov 2005 00:27:51 +0000
Reply-To: <andy@areyoulocal.co.uk>
From: Andy Ballingall <andy@areyoulocal.co.uk>
To: <pgsql-sql@postgresql.org>
Subject: Is it possible to redirect an update/insert/delete to a different
	table?
Date: Sun, 20 Nov 2005 00:27:04 -0000
Organization: Areyoulocal Ltd.
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_0038_01C5ED69.21EC2400"
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcXtaSHKo4dqIR2STTetnP1lML+ESg==
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Message-ID: <ECOWS06MsMkXWNcu9mt0001b868@smtp-out6.blueyonder.co.uk>
X-OriginalArrivalTime: 20 Nov 2005 00:27:51.0170 (UTC)
	FILETIME=[3DA80E20:01C5ED69]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.001 required=5 tests=[AWL=-0.001,
	HTML_MESSAGE=0.001]
X-Spam-Score: 0.001
X-Spam-Level: 
X-Archive-Number: 200511/160
X-Sequence-Number: 23411

This is a multi-part message in MIME format.

------=_NextPart_000_0038_01C5ED69.21EC2400
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hello,

 

Is there a way that I can specify that a request to change to a particular
table is redirected instead 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.

 

Thanks

Andy Ballingall


------=_NextPart_000_0038_01C5ED69.21EC2400
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:Arial;
	color:windowtext;}
@page Section1
	{size:595.3pt 841.9pt;
	margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-GB link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Hello,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'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 instead to a different =
table?<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I&#8217;ve looked through rules, and as far as I can =
make
out, they are only useful for explicit actions. I&#8217;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.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Thanks<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Andy Ballingall<o:p></o:p></span></font></p>

</div>

</body>

</html>

------=_NextPart_000_0038_01C5ED69.21EC2400--




From juancri@gmail.com Sun Nov  20 06:43:43 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id ADEA9DB7DB
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 02:43:43 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 74472-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 06:43:42 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from floppy.pyrenet.fr (floppy.pyrenet.fr [194.116.145.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 7E56DDBC29
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 02:43:40 -0400 (AST)
Received: by floppy.pyrenet.fr (Postfix, from userid 106)
	id C6FEC31059; Sun, 20 Nov 2005 07:43:39 +0100 (MET)
From: "JuanCri" <juancri@gmail.com>
X-Newsgroups: pgsql.sql
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Date: 19 Nov 2005 22:43:33 -0800
Organization: http://groups.google.com
Lines: 15
Message-ID: <1132469013.246334.171700@g49g2000cwa.googlegroups.com>
References: <ECOWS06MsMkXWNcu9mt0001b868@smtp-out6.blueyonder.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Complaints-To: groups-abuse@google.com
In-Reply-To: <ECOWS06MsMkXWNcu9mt0001b868@smtp-out6.blueyonder.co.uk>
User-Agent: G2/0.2
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
	rv:1.7.12) Gecko/20050915 Firefox/1.0.7,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: g49g2000cwa.googlegroups.com; posting-host=201.215.213.22;
	posting-account=7tws-Q0AAADpX36azeU8JBvtMMi8oYJs
To: pgsql-sql@postgresql.org
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/251
X-Sequence-Number: 23502

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




From peter_e@gmx.net Sun Nov  20 11:07:05 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6C2BDD6F49
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 07:07:05 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 58064-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 11:07:06 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.gmx.net (mail.gmx.net [213.165.64.20])
	by svr1.postgresql.org (Postfix) with SMTP id 9D118D6DC2
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 07:07:01 -0400 (AST)
Received: (qmail invoked by alias); 20 Nov 2005 11:07:02 -0000
Received: from dslb-084-063-030-177.pools.arcor-ip.net (EHLO colt.pezone.net)
	[84.63.30.177]
	by mail.gmx.net (mp011) with SMTP; 20 Nov 2005 12:07:02 +0100
X-Authenticated: #495269
From: Peter Eisentraut <peter_e@gmx.net>
To: andy@areyoulocal.co.uk
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Date: Sun, 20 Nov 2005 12:07:00 +0100
User-Agent: KMail/1.8.2
References: <ECOWS06MsMkXWNcu9mt0001b868@smtp-out6.blueyonder.co.uk>
In-Reply-To: <ECOWS06MsMkXWNcu9mt0001b868@smtp-out6.blueyonder.co.uk>
Cc: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200511201207.01114.peter_e@gmx.net>
X-Y-GMX-Trusted: 0
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/165
X-Sequence-Number: 23416

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/



From systemguards@gmail.com Sun Nov  20 14:23:28 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 84910D70A1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 10:23:28 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 85791-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 14:23:31 +0000 (GMT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.203])
	by svr1.postgresql.org (Postfix) with ESMTP id 96DFDD79C7
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 10:23:25 -0400 (AST)
Received: by wproxy.gmail.com with SMTP id 57so191583wri
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 06:23:30 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references;
	b=GRUY069JSplXlyMnR2Qxksa/lBCw2ajn9k20rkloiDh+RvEOAGksaJCS7xfzY+4FjxUOU0LgK29ZDWEdYnEuOe4EgFAG4U+TcIseJe3nIbCUsCi5NEWg+SUY2c1bO7QT+FESyqnfo96dxAcTvbX7hAmOnPiyjj/Dq9560IiMoTI=
Received: by 10.65.186.13 with SMTP id n13mr1821753qbp;
	Sun, 20 Nov 2005 06:23:30 -0800 (PST)
Received: by 10.65.180.2 with HTTP; Sun, 20 Nov 2005 06:23:29 -0800 (PST)
Message-ID: <c2d9e70e0511200623w6e90d15bt64a03a2cfcd7132b@mail.gmail.com>
Date: Sun, 20 Nov 2005 09:23:29 -0500
From: Jaime Casanova <systemguards@gmail.com>
To: andy@areyoulocal.co.uk
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Cc: pgsql-sql@postgresql.org
In-Reply-To: <ECOWS06MsMkXWNcu9mt0001b868@smtp-out6.blueyonder.co.uk>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
References: <ECOWS06MsMkXWNcu9mt0001b868@smtp-out6.blueyonder.co.uk>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.022 required=5 tests=[AWL=0.022]
X-Spam-Score: 0.022
X-Spam-Level: 
X-Archive-Number: 200511/167
X-Sequence-Number: 23418

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



From andy@areyoulocal.co.uk Sun Nov  20 15:28:01 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id D8447D6E72
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 11:28:01 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 90710-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 15:28:05 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp-out2.blueyonder.co.uk (smtp-out2.blueyonder.co.uk
	[195.188.213.5])
	by svr1.postgresql.org (Postfix) with ESMTP id B508CD6810
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 11:27:58 -0400 (AST)
Received: from Eve ([82.43.185.102]) by smtp-out2.blueyonder.co.uk with
	Microsoft SMTPSVC(5.0.2195.6713); Sun, 20 Nov 2005 15:28:56 +0000
Reply-To: <andy@areyoulocal.co.uk>
From: Andy Ballingall <andy@areyoulocal.co.uk>
To: "'Peter Eisentraut'" <peter_e@gmx.net>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Date: Sun, 20 Nov 2005 15:28:10 -0000
Organization: Areyoulocal Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcXtwqnp/gckzGu9T6WyvgK/APYi2wAJAyJg
In-Reply-To: <200511201207.01114.peter_e@gmx.net>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Message-ID: <ECOWS02MmWL9vVmguzY0004c4cf@smtp-out2.blueyonder.co.uk>
X-OriginalArrivalTime: 20 Nov 2005 15:28:56.0226 (UTC)
	FILETIME=[1EF0E820:01C5EDE7]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/168
X-Sequence-Number: 23419

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





From andy@areyoulocal.co.uk Sun Nov  20 15:40:31 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id CFE21D7AC5
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 11:40:31 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 90596-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 15:40:35 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp-out2.blueyonder.co.uk (smtp-out2.blueyonder.co.uk
	[195.188.213.5])
	by svr1.postgresql.org (Postfix) with ESMTP id EFECFD7C47
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 11:40:28 -0400 (AST)
Received: from Eve ([82.43.185.102]) by smtp-out2.blueyonder.co.uk with
	Microsoft SMTPSVC(5.0.2195.6713); Sun, 20 Nov 2005 15:41:27 +0000
Reply-To: <andy@areyoulocal.co.uk>
From: Andy Ballingall <andy@areyoulocal.co.uk>
To: "'Jaime Casanova'" <systemguards@gmail.com>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Date: Sun, 20 Nov 2005 15:40:41 -0000
Organization: Areyoulocal Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcXt3hyzIjxuelHqQmeWq0aR+gRpBwACZpcg
In-Reply-To: <c2d9e70e0511200623w6e90d15bt64a03a2cfcd7132b@mail.gmail.com>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Message-ID: <ECOWS02M7sInp0vS0DO0004c584@smtp-out2.blueyonder.co.uk>
X-OriginalArrivalTime: 20 Nov 2005 15:41:27.0132 (UTC)
	FILETIME=[DE8411C0:01C5EDE8]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/170
X-Sequence-Number: 23421

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





From akretschmer@spamfence.net Sun Nov  20 16:12:42 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 2DE84D7E2C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 12:12:42 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 97720-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 16:12:40 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from 137086.vserver.de (vs137086.vserver.de [62.75.137.86])
	by svr1.postgresql.org (Postfix) with ESMTP id 361DBD72C3
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 12:12:38 -0400 (AST)
Received: from a-kretschmer.de (p54B39330.dip0.t-ipconnect.de [84.179.147.48])
	(authenticated bits=0)
	by 137086.vserver.de (8.12.8/8.12.8) with ESMTP id jAKGCZuk007266
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 17:12:36 +0100
Received: from kretschmer by a-kretschmer.de with local (Exim 3.36 #1)
	id 1EdrsR-00014C-00
	for pgsql-sql@postgresql.org; Sun, 20 Nov 2005 17:17:15 +0100
Date: Sun, 20 Nov 2005 17:17:15 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Message-ID: <20051120161715.GA2832@kaufbach.delug.de>
References: <200511201207.01114.peter_e@gmx.net>
	<ECOWS02MmWL9vVmguzY0004c4cf@smtp-out2.blueyonder.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <ECOWS02MmWL9vVmguzY0004c4cf@smtp-out2.blueyonder.co.uk>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B  3B86 4DB3 D9EE 7F45 84DA
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
X-Lugdd: Gerd Kube
X-Info: My name is root. Just root. And I am licensed to kill -9
User-Agent: mutt-ng 1.5.9i (Linux)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/171
X-Sequence-Number: 23422

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°



From andy@areyoulocal.co.uk Sun Nov  20 16:37:50 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 8C5F9D7A52
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 12:37:50 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 98848-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 16:37:48 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp-out5.blueyonder.co.uk (smtp-out5.blueyonder.co.uk
	[195.188.213.8])
	by svr1.postgresql.org (Postfix) with ESMTP id 81D06D78F7
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 12:37:47 -0400 (AST)
Received: from Eve ([82.43.185.102]) by smtp-out5.blueyonder.co.uk with
	Microsoft SMTPSVC(5.0.2195.6713); Sun, 20 Nov 2005 16:38:39 +0000
Reply-To: <andy@areyoulocal.co.uk>
From: Andy Ballingall <andy@areyoulocal.co.uk>
To: "'Andreas Kretschmer'" <akretschmer@spamfence.net>,
	<pgsql-sql@postgresql.org>
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Date: Sun, 20 Nov 2005 16:37:54 -0000
Organization: Areyoulocal Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcXt7o/F9v+KhrkhQ9GgPGceVyiPpgAAKmzQ
In-Reply-To: <20051120161715.GA2832@kaufbach.delug.de>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Message-ID: <ECOWS05MDz4WolWa4lU00023f54@smtp-out5.blueyonder.co.uk>
X-OriginalArrivalTime: 20 Nov 2005 16:38:39.0843 (UTC)
	FILETIME=[DC923330:01C5EDF0]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/172
X-Sequence-Number: 23423

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,
>=20
> I'm glad it's possible, but I can't see how from the documentation.
>=20
> Say if I have a table called 'apples' and a table called 'pears'.
>=20
> 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=3D NEW.name where id=3DNEW.id ;

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

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

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


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

HTH, Andreas
--=20
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=B0, E =
13.56889=B0

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



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





From akretschmer@spamfence.net Sun Nov  20 16:38:01 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id A675AD7D23
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 12:38:01 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 98679-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 16:37:59 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from 137086.vserver.de (vs137086.vserver.de [62.75.137.86])
	by svr1.postgresql.org (Postfix) with ESMTP id CDC48D7AD3
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 12:37:57 -0400 (AST)
Received: from a-kretschmer.de (p54B39330.dip0.t-ipconnect.de [84.179.147.48])
	(authenticated bits=0)
	by 137086.vserver.de (8.12.8/8.12.8) with ESMTP id jAKGbsXD023360
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 17:37:55 +0100
Received: from kretschmer by a-kretschmer.de with local (Exim 3.36 #1)
	id 1EdsGw-00016l-00
	for pgsql-sql@postgresql.org; Sun, 20 Nov 2005 17:42:34 +0100
Date: Sun, 20 Nov 2005 17:42:34 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Message-ID: <20051120164234.GB2832@kaufbach.delug.de>
References: <c2d9e70e0511200623w6e90d15bt64a03a2cfcd7132b@mail.gmail.com>
	<ECOWS02M7sInp0vS0DO0004c584@smtp-out2.blueyonder.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <ECOWS02M7sInp0vS0DO0004c584@smtp-out2.blueyonder.co.uk>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B  3B86 4DB3 D9EE 7F45 84DA
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
X-Lugdd: Gerd Kube
X-Info: My name is root. Just root. And I am licensed to kill -9
User-Agent: mutt-ng 1.5.9i (Linux)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/173
X-Sequence-Number: 23424

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°



From akretschmer@spamfence.net Sun Nov  20 16:57:13 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 31525D7D9B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 12:57:13 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 08896-05
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 16:57:11 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from 137086.vserver.de (vs137086.vserver.de [62.75.137.86])
	by svr1.postgresql.org (Postfix) with ESMTP id 5DE6CD7DB4
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 12:57:10 -0400 (AST)
Received: from a-kretschmer.de (p54B39330.dip0.t-ipconnect.de [84.179.147.48])
	(authenticated bits=0)
	by 137086.vserver.de (8.12.8/8.12.8) with ESMTP id jAKGv2TV013669
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 17:57:08 +0100
Received: from kretschmer by a-kretschmer.de with local (Exim 3.36 #1)
	id 1EdsZM-0001A1-00
	for pgsql-sql@postgresql.org; Sun, 20 Nov 2005 18:01:36 +0100
Date: Sun, 20 Nov 2005 18:01:36 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-sql@postgresql.org
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Message-ID: <20051120170136.GC2832@kaufbach.delug.de>
References: <20051120161715.GA2832@kaufbach.delug.de>
	<ECOWS05MDz4WolWa4lU00023f54@smtp-out5.blueyonder.co.uk>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <ECOWS05MDz4WolWa4lU00023f54@smtp-out5.blueyonder.co.uk>
X-OS: Debian/GNU Linux - weil ich es mir Wert bin!
X-GPG-Fingerprint: EE16 3C01 7B9C 10F7 2C8B  3B86 4DB3 D9EE 7F45 84DA
X-Message-Flag: "Windows" is not the answer. "Windows" is the question and the
	answer is "no"!
X-Lugdd: Gerd Kube
X-Info: My name is root. Just root. And I am licensed to kill -9
User-Agent: mutt-ng 1.5.9i (Linux)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/174
X-Sequence-Number: 23425

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°



From andy@areyoulocal.co.uk Sun Nov  20 17:15:17 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id CB0A5D7D62
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 13:15:17 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 09416-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 17:15:16 +0000 (GMT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp-out1.blueyonder.co.uk (smtp-out1.blueyonder.co.uk
	[195.188.213.4])
	by svr1.postgresql.org (Postfix) with ESMTP id 0652AD7A52
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 13:15:14 -0400 (AST)
Received: from Eve ([82.43.185.102]) by smtp-out1.blueyonder.co.uk with
	Microsoft SMTPSVC(5.0.2195.6713); Sun, 20 Nov 2005 17:16:08 +0000
Reply-To: <andy@areyoulocal.co.uk>
From: Andy Ballingall <andy@areyoulocal.co.uk>
To: "'Andreas Kretschmer'" <akretschmer@spamfence.net>,
	<pgsql-sql@postgresql.org>
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Date: Sun, 20 Nov 2005 17:15:22 -0000
Organization: Areyoulocal Ltd.
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
X-Mailer: Microsoft Office Outlook, Build 11.0.5510
Thread-Index: AcXt8cIkpAxp8YhQSMa4ued2NE0T1wAAr7ow
In-Reply-To: <20051120164234.GB2832@kaufbach.delug.de>
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Message-ID: <ECOWS01MabrDy4i5oln0004dff7@smtp-out1.blueyonder.co.uk>
X-OriginalArrivalTime: 20 Nov 2005 17:16:08.0085 (UTC)
	FILETIME=[18A0B450:01C5EDF6]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[AWL=0.000]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200511/175
X-Sequence-Number: 23426

>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





From aklaver@comcast.net Sun Nov  20 23:41:16 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 55C70D7080
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 19:41:16 -0400 (AST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 75117-08
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 20 Nov 2005 23:41:18 +0000 (GMT)
X-Greylist: delayed 00:05:10.012857 by SQLgrey-
Received: from rwcrmhc11.comcast.net (rwcrmhc11.comcast.net [216.148.227.117])
	by svr1.postgresql.org (Postfix) with ESMTP id C574CD6E94
	for <pgsql-sql@postgresql.org>; Sun, 20 Nov 2005 19:41:13 -0400 (AST)
Received: from [192.168.0.9]
 (c-24-17-161-80.hsd1.wa.comcast.net[24.17.161.80])
	by comcast.net (rwcrmhc11) with ESMTP
	id <2005112023360401300c0rr7e>; Sun, 20 Nov 2005 23:36:05 +0000
From: Adrian Klaver <aklaver@comcast.net>
Reply-To: aklaver@comcast.net
To: pgsql-sql@postgresql.org,
 <andy@areyoulocal.co.uk>
Subject: Re: Is it possible to redirect an update/insert/delete to a different
	table?
Date: Sun, 20 Nov 2005 15:35:57 -0800
User-Agent: KMail/1.6.2
Cc: "'Andreas Kretschmer'" <akretschmer@spamfence.net>
References: <ECOWS01MabrDy4i5oln0004dff7@smtp-out1.blueyonder.co.uk>
In-Reply-To: <ECOWS01MabrDy4i5oln0004dff7@smtp-out1.blueyonder.co.uk>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <200511201535.57300.aklaver@comcast.net>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=2.319 required=5 tests=[DNS_FROM_RFC_POST=1.44,
	DNS_FROM_RFC_WHOIS=0.879]
X-Spam-Score: 2.319
X-Spam-Level: **
X-Archive-Number: 200511/177
X-Sequence-Number: 23428

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