X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.2])
	by svr1.postgresql.org (Postfix) with ESMTP id 790EDD1D412
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 Feb 2004 17:41:42 +0000 (GMT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024)
	with ESMTP id 33911-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon,  9 Feb 2004 13:41:30 -0400 (AST)
Received: from crommail.cromwell-tools.co.uk
	(host217-40-164-180.in-addr.btopenworld.com [217.40.164.180])
	by svr1.postgresql.org (Postfix) with ESMTP id BB933D1C98E
	for <pgsql-sql@postgresql.org>; Mon,  9 Feb 2004 13:41:28 -0400 (AST)
Message-ID: <4027C681.3090102@cromwell.co.uk>
Date: Mon, 09 Feb 2004 17:42:25 +0000
From: Mark Gibson <gibsonm@cromwell.co.uk>
MIME-Version: 1.0
To: Richard Sydney-Smith <richard@ibisaustralia.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Implementation of a bag pattern using rules
References: <40278022.5070408@cromwell.co.uk>
	<001b01c3ef1c$a3034890$cee98a90@athlon2000>
In-Reply-To: <001b01c3ef1c$a3034890$cee98a90@athlon2000>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at postgresql.org
X-Archive-Number: 200402/88
X-Sequence-Number: 16779

Richard Sydney-Smith wrote:

>Mark,
>
>love the idea, guess I should have read it somewhere but haven't. Obvious
>and beautiful. Please let me know if you or someone else solves the initial
>double value.
>
>  
>
I used to use functions for this kind of thing,
and was thinking that what SQL really needed was an 'UPDATE OR INSERT' 
command,
then it suddenly came to me last night, it could be done with rules or 
triggers.
[I've posted a trigger solution for the relative values separately, in 
response to Tom Lanes help]

>Got me thinking of all the places I cold have used this instead of coding
>select/insert/update/delete.
>Also have you worked a solutions where both the abs and relative inserts
>apply to the same bag
>
>eg insert another apple vs set apples to 5
>
>  
>
Hmmm, yeah, I'm wondering about that one. It would be handy.
Custom datatype maybe - an integer with a flag to indicate absolute or 
relative???
eg:
INSERT INTO bag_test VALUES ('orange', '10 abs');
INSERT INTO bag_test VALUES ('orange', '-5 rel');

or views that modify an underlying table???
eg:
INSERT INTO bag_test_abs VALUES ('orange', 10);
INSERT INTO bag_test_rel VALUES ('orange', -5);

I have no idea yet whether these are possible though, any ideas?

>Much of my attitude to triggers has been non-committal. Your example changes
>that.
>
>  
>
Triggers, rules and functions ROCK. It's allowed us to move all the 
business logic into the
database itself so we can create really simple clients easily in any 
language/environment.

Right, I'm off home now :)

Cheers

-- 
Mark Gibson <gibsonm |AT| cromwell |DOT| co |DOT| uk>
Web Developer & Database Admin
Cromwell Tools Ltd.
Leicester, England.