[SQL] Update timestamp on update

[Jeff Williams]

I have a table like:

CREATE TABLE products (
id int,
status int,
last_status_change timestamp DEFAULT now()
);

What I would like is that whenever the status is changed the
last_status_change timestamp is updated to the current time. I have had
a look at the rules and what I want would be similar to:

CREATE RULE last_status_change AS ON UPDATE
TO products WHERE NEW.status <> OLD.status
DO UPDATE products SET last_status_change = now() WHERE id = OLD.id;

Except of course that the above is recursive and doesn't work.

How can I do this?

Jeff

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] Update timestamp on update

[Jeff Williams]

Tom Lane wrote:

>Jeff Williams <[EMAIL PROTECTED]> writes:
>  
>
>>last_status_change timestamp DEFAULT now()
>>
>>
>
>  
>
>>What I would like is that whenever the status is changed the
>>last_status_change timestamp is updated to the current time.
>>
>>
>
>For this you use an ON UPDATE trigger; rules are not a good way to solve
>it.  See the documentation about triggers.  The first example on this
>page does it along with a few other things:
>http://developer.postgresql.org/docs/postgres/plpgsql-trigger.html
>  
>
Thanks. Triggers was my first thought, but chapter 35 on Triggers didn't
really indicate a way I could do this easily and scared me with a lot of
c code. Maybe it is a good idea to present some of the more common
things you would want to do with triggers in the triggers chapter?

Jeff

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [SQL] PERSISTANT PREPARE (another point of view)

[Jeff Williams]

On 20/07/08 22:16, Milan Oparnica wrote:
> Try to write following simple scenario:
>
> a. Data is retrieved from two tables in INNER JOIN
> b. I don't need all fields, but just some of them from both tables
>
> Lets call tables Customers and Orders.
>
> Definition of tables are:
> Customers (CustomID INTEGER, Name TEXT(50), Adress TEXT(100))
> Orders (OrderID INTEGER, CustomID INTEGER, OrderNum TEXT(10))
>
> Now I need a list of order numbers for some customer:
>
> SELECT C.CustomID, C.Name, O.OrderNum
> FROM Customers C INNER JOIN Orders  O ON C.CustomID=O.CustomID
> WHERE C.Name LIKE 
>
You can do this with cursors, but I'm not sure if you still get the
query caching?

CREATE FUNCTION test(refcursor, input varchar) RETURNS refcursor AS $$
BEGIN
OPEN $1 FOR SELECT C.CustomID, C.Name, O.OrderNum
 FROM Customers C INNER JOIN Orders  O ON C.CustomID=O.CustomID
 WHERE C.Name LIKE '%' || input || '%';
RETURN $1;
END
$$ LANGUAGE plpgsql;

Then to use:

BEGIN;
SELECT test('curs', );
FETCH ALL FROM curs;
END;

Jeff

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