Re: [SQL] MAX() of 0 records.

Hi Tom,

> Hmm. There are several things going on here, but one thing that needs
> clarification is whether this UPDATE is written correctly. Since it

My goofup (you said you wanted a compact script!).
You are correct there should have been an extra where condition in
the triggered function.
====
UPDATE entry_stats
SET entry_minprice=min(item_price)
FROM item where item_entry_id=NEW.item_entry_id AND item_live='f';
====
Should really have been:
====
UPDATE entry_stats
SET entry_minprice=min(item_price)
FROM item where item_entry_id=NEW.item_entry_id
AND entry_stats.entry_id=item_entry_id
AND item_live='f';
====
which still generates the same error message (as the 'problem' is
caused by the where clause, not what is being updated).

FWIW I've attached the real function that I've implemented to get
around the error message. In all probability the way I'm handling
it is the right way:

1. Check I'm going to get a valid response from my aggregate
2a. If so perform the update with the aggregate
2b. If not perform the update with zeros(default value)

Originally I was just wondering if I could do it all in one go,
Try to perform the update and automatically get the aggregate
result if it were 'available' and default to zeros if not.

If I forget about aggregate functions for a moment and just
consider an update where nothing matches the where criterion
then I'd still use the same logic above to reset the values
to their default. The only differences between using the
aggregate function and not is that one throws an error and
the other just updates 0 rows.

> The thing that jumps out at me is that if you actually try the SELECT
> illustrated above, you do not get any row, null or otherwise; you get
> ERROR: Attribute entry_stats.ctid must be GROUPed or used in an
> aggregate function
> which is a mighty valid complaint. If you are aggregating rows to get
> the MIN() then you don't have a unique ctid to deliver, so which row
> ought to be updated? This is the system's way of expressing the same
> concern I started with: this query doesn't seem to be well-posed.
>
> You don't see this complaint when you try the UPDATE, because ctid
> is added to the implicit select result in a back-door way that doesn't
> get checked for GROUP-BY validity. I wonder whether that is the bug.
> If so, we'd basically be saying that no query like this is valid
> (since UPDATE doesn't have a GROUP BY option, there'd be no way to
> pass the grouping check).

Would that mean that any update that used an aggregate function
would be invalid? That would be a bit scary seeing as I am doing
this in part to get around using aggregate functions in a view.

> Another way to look at it is that perhaps an UPDATE involving aggregate
> functions ought to be implicitly treated as GROUP BY targetTable.ctid.

What exactly is a ctid?

Thanks for your response Tom, it has been enlightening. I feel I'm
getting a better understanding of what's going inside pgsql by the
day from yourself and other peoples posts on the various lists.

--
Paul McGarry mailto:paulm(at)opentec(dot)com(dot)au
Systems Integrator http://www.opentec.com.au
Opentec Pty Ltd http://www.iebusiness.com.au
6 Lyon Park Road Phone: (02) 9878 1744
North Ryde NSW 2113 Fax: (02) 9878 1755