Skip site navigation (1) Skip section navigation (2)

Re: Best practices?

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: David Fetter <david(at)fetter(dot)org>
Cc: sfpug(at)postgresql(dot)org
Subject: Re: Best practices?
Date: 2003-10-01 05:38:48
Message-ID: 200309302238.48856.josh@agliodbs.com (view raw or flat)
Thread:
Lists: sfpug
Dave,

> At the end of any transaction involving foo (including INSERT), a foo
> must have between m and n bars.
>
> Is there a standard way to approach this?

Not a good way, no.   What you need is a constraint, or a BEFORE trigger, that 
only fires once at the end of each transaction.   Even with the statement 
triggers coming in 7.4, we don't have that.

Using VIEWs backed by RULEs won't work either, becuase if N is 2 and M is 5, 
you need to insert at least 3 bars for each foo in any statement, an 
impossibility in SQL.

(FWIW, such constraints are one of the major failings of the SQL standard ... 
they are easy to implement in relational calculus, but can only be done 
procedurally in SQL)

Unfortunately, I think you're facing using "data-push" functions, where 
instead of "insert into foo; insert into bar" you call fn_insert_foo ( foo1, 
foo2, {barA1, barA2}, {barB1, barB2});

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

sfpug by date

Next:From: Josh BerkusDate: 2003-10-01 05:40:19
Subject: Desperately seeking office space
Previous:From: David FetterDate: 2003-09-30 19:13:00
Subject: Best practices?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group