How best to implement a multi-table constraint?

Is something like this possible?
CREATE testsetof( IN toad_id integer) RETURNS SETOF road_table, SETOF int4
AS $BODY$....

If yes, is this the way to do 'RETURN NEXT'?
RETURN NEXT road_table_row, an_integer;

Thanks
CYW

Hello

plpgsql should return only one set. You should to returns set of
cursors - that is real multisets.
http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html

regards
Pavel Stehule

2008/10/21 <cyw@dls.net>:

Why do you need to store the total area at all (property_area)? This
value can easily be calculated with an group by query.

I agree, but it seems to me that property_area isn't a strictly derived
value.

It's possible to change the makeup of a property's landcover -- for
example, remove some concrete and plant more grass, or add an extension to
the building -- but the overall property area should remain constant. I
feel like I should probably include some kind of constraint to enforce
this.

Am I needlessly over-complicating this?

-Karl

Karl Nack

Futurity, Inc.
773-506-2007

----- Original Message -----
From: matthias@yacc.se
Sent: Tue, October 21, 2008 7:31
Subject:Re: [GENERAL] How best to implement a multi-table constraint?

Why do you need to store the total area at all (property_area)? This
value can easily be calculated with an group by query.

On Mon, Oct 20, 2008 at 10:56 PM, Karl Nack <pglists@futurityinc.com> wrote:

Hello all,

I'm a bit of a newb designing a database to hold landcover information for
properties in a city. Here's some simple sample data:

property:
property_name*, property_area
-----------------------------
sample house, 2500

property_landcover:
property_name*, landcover_name*, landcover_area
-----------------------------------------------
sample house, building, 1000
sample house, grass, 1000
sample house, concrete, 500

Now, I need to check that the sum of landcover_area for a property matches
the property_area.

It seems like I have three obvious options:

1. A constraint trigger that sums up landcover area and compares it to the
property area.

Downside: The trigger will run for every row that's updated in these two
tables, although it only needs to run once for each property.

2. A statement-level trigger that does the same thing as #1.

Downside: Since I don't have access to the updated rows, I'll have to
check the entire property table against the entire property_landcover
table. It seems like this could get expensive if either of these tables
gets very large.

3. Use a 3rd table to hold the total landcover area for each property. Use
row-level triggers to keep this 3rd table updated. Use a statement-level
trigger (or table constraint) to ensure the total landcover area matches
the property area.

Downside: Although I avoid redundant checks, my understanding is that
UPDATE is a fairly expensive operation, so it might not actually perform
any better.

Although my tables are small right now, they may potentially have to hold
an entire city's worth of properties, so I'm interested in finding a
solution that scales.

Can anyone offer some feedback or suggestions on which of these options to
use? Or perhaps even another solution that hasn't occurred to me?

Thanks!

-Karl

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

----- End of original message -----

I agree, but it seems to me that property_area isn't a strictly derived
value.

It's possible to change the makeup of a property's landcover -- for
example, remove some concrete and plant more grass, or add an extension to
the building -- but the overall property area should remain constant. I
feel like I should probably include some kind of constraint to enforce
this.

Am I needlessly over-complicating this?

-Karl

----- Original Message -----
From: matthias@yacc.se
Sent: Tue, October 21, 2008 7:31
Subject:Re: [GENERAL] How best to implement a multi-table constraint?

Why do you need to store the total area at all (property_area)? This
value can easily be calculated with an group by query.

On Mon, Oct 20, 2008 at 10:56 PM, Karl Nack <pglists@futurityinc.com> wrote:

Hello all,

I'm a bit of a newb designing a database to hold landcover information for
properties in a city. Here's some simple sample data:

property:
property_name*, property_area
-----------------------------
sample house, 2500

property_landcover:
property_name*, landcover_name*, landcover_area
-----------------------------------------------
sample house, building, 1000
sample house, grass, 1000
sample house, concrete, 500

Now, I need to check that the sum of landcover_area for a property matches
the property_area.

It seems like I have three obvious options:

1. A constraint trigger that sums up landcover area and compares it to the
property area.

Downside: The trigger will run for every row that's updated in these two
tables, although it only needs to run once for each property.

2. A statement-level trigger that does the same thing as #1.

Downside: Since I don't have access to the updated rows, I'll have to
check the entire property table against the entire property_landcover
table. It seems like this could get expensive if either of these tables
gets very large.

3. Use a 3rd table to hold the total landcover area for each property. Use
row-level triggers to keep this 3rd table updated. Use a statement-level
trigger (or table constraint) to ensure the total landcover area matches
the property area.

Downside: Although I avoid redundant checks, my understanding is that
UPDATE is a fairly expensive operation, so it might not actually perform
any better.

Although my tables are small right now, they may potentially have to hold
an entire city's worth of properties, so I'm interested in finding a
solution that scales.

Can anyone offer some feedback or suggestions on which of these options to
use? Or perhaps even another solution that hasn't occurred to me?

Thanks!

-Karl

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

----- End of original message -----