Can PL/PGSQL function return multiple SETOFs

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

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(at)dls(dot)net>:
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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(at)futurityinc(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>