Re: [NOVICE] Need help with a query

UPDATE ok if x, y always go in foo first.

Efficiency depends on whether the view is called more frequently than the updates, and setting up of indexes. In my case the new records are constant and views occasional.

Trigger function would need to check that x, y wasn't already in table.

----- Reply message -----
From: "A B" <gentosaker(at)gmail(dot)com>
Date: Tue, Oct 26, 2010 8:05 am
Subject: [NOVICE] Need help with a query
To: "scorpdaddy(at)hotmail(dot)com" <scorpdaddy(at)hotmail(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>

in this case new x-y pairs always goes into foo first.
One could consider creating a view, but would a view be efficient in
this case? A new row in bar, would that not cause it to select max()
value for the x,y pair again, i.e. scan the table bar for the specific
x,y values, or will it be smart enough to do only one comparison?

2010/10/26 scorpdaddy(at)hotmail(dot)com <scorpdaddy(at)hotmail(dot)com>:
> Is it necessary that this is a table? Because it seems a lot like a view of
> the same data actually. CREATE VIEW ...
>
> The expected UPDATE query for a table can be problematic. I have a similar
> issue in 1 of my DB's.  New records - new x, y - get written to bar and do
> not yet have a corresponding x, y record in foo.  So when the UPDATE runs it
> ignores the new records.  One can have an after insert trigger to keep foo
> up to date with bar. But such machinations arise because foo is only a view
> of the same data in bar.
>
>
>
> ----- Reply message -----
> From: "A B" <gentosaker(at)gmail(dot)com>
> Date: Tue, Oct 26, 2010 6:16 am
> Subject: [NOVICE] Need help with a query
> To: <pgsql-novice(at)postgresql(dot)org>
>
> Hello.
>
> I'm having a problem with  a probably very simple query.
> I need to update a table
> foo (x int, y int, last_seen timestamp, unique(x,y));
>
> where the values should be taken from a larger table
> bar( x int, y int, seen timestamp);
> where each x,y combination occurs several times, and the value in
> foo.last_seen should be the value max(seen) for each pair of x,y.
> Notice! All combinations of x,y in bar are also in foo, but not the
> other way around.
>
> So how do I write a query for this?
> Thanks in advance.
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>
>
>
>