declare column update expression

Hello,

2008/9/11 Chris Velevitch <chris.velevitch@gmail.com>:

In 7.4, how do I declare that a column in a table is to be
automatically set to the value of some fixed expression whenever a row
is updated?

Eg column last_modified is always set to current_timestamp

you should to use trigger

regards
Pavel Stehule

On Thu, Sep 11, 2008 at 6:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

you should to use trigger

I've never used trigger before, it looks messy and error prone having
to write functions.

How is it that you can declare the default value of a column on insert
but not on update?

Chris
--
Chris Velevitch
Manager - Adobe Platform Users Group, Sydney
m: 0415 469 095
www.apugs.org.au

Adobe Platform Users Group, Sydney
September meeting: It's Going To Be Brilliant
Date: Mon 29th September 6pm for 6:30 start
Details and RSVP on http://apugs2008september.eventbrite.com

On Thu, Sep 11, 2008 at 1:35 AM, Chris Velevitch
<chris.velevitch@gmail.com> wrote:

In 7.4, how do I declare that a column in a table is to be
automatically set to the value of some fixed expression whenever a row
is updated?

Eg column last_modified is always set to current_timestamp

A trigger as Pavel said. writing them in plpgsql seems a bit hard at
first, but it's a simple language and it's pretty easy to write stuff
like this in.

Here's a simple example of last modified trigger using plpgsql from way back:

-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
BEGIN
new.lm :=''now'';
RETURN new;
END;
' LANGUAGE 'plpgsql';

-- TABLE --

CREATE TABLE dtest (
id int primary key,
fluff text,
lm timestamp without time zone
);

--TRIGGER --

CREATE TRIGGER dtest
BEFORE UPDATE or INSERT ON dtest FOR EACH ROW EXECUTE PROCEDURE
modtime(lm);

-- SQL TESTS --

INSERT INTO dtest (id, fluff) VALUES (1,'this is a test');
INSERT INTO dtest (id, fluff) VALUES (2,'this is another test');
SELECT * FROM dtest;
1 | this is a test | 2003-04-02 10:33:12.577089
2 | this is another test | 2003-04-02 10:33:18.591148
UPDATE dtest SET id=3 WHERE id=1;
3 | this is a test | 2003-04-02 10:34:52.219963 [1]The timestamp has changed for this record when we changed the id field.
UPDATE dtest SET fluff='now is the time' WHERE id=2;
SELECT * FROM dtest WHERE id=2;
2 | now is the time | 2003-04-02 10:38:06.259443 [2]The timestamp also changes for the fluff field.
UPDATE dtest SET lm='2003-04-02 08:30:00' WHERE id=3;
SELECT * FROM dtest WHERE id=3;
3 | this is a test | 2003-04-02 10:36:15.45687 [3]We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it

[1]: The timestamp has changed for this record when we changed the id field.
[2]: The timestamp also changes for the fluff field.
[3]: We tried to set lm, but the trigger on that field in dtest intercepted the change and forced it
intercepted the change and forced it

On Thu, Sep 11, 2008 at 1:35 AM, Chris Velevitch
<chris.velevitch@gmail.com> wrote:

In 7.4, how do I declare that a column in a table is to be
automatically set to the value of some fixed expression whenever a row
is updated?

Oh yeah, if you can upgrade from 7.4 that would be a good idea, it's
getting a bit old and the newer versions of pgsql like 8.3 are
literally many times faster at most things. We just upgraded from 8.1
to 8.3 and resolved a lot of performance issues, I can't imagine how
slow it would be running 7.4 nowadays.

How is it that you can declare the default value of a column on insert
but not on update?

You can do this without triggers (at least on 8.3).

UPDATE foo
SET field1 = 2,
field2 = default
WHERE field3 = 22

I just tested it and it will set the value back to the default. The
caveat here is that it won't FORCE the value like it would with a
trigger. So while the trigger would happen automatically, using this
approach, you'd have to remember to also update that field any time you
did an update.

Artacus

On Fri, Sep 12, 2008 at 12:50 PM, Artacus <artacus@comcast.net> wrote:

You can do this without triggers (at least on 8.3).

UPDATE foo
SET field1 = 2,
field2 = default
WHERE field3 = 22

That means I have to then go through all my code and make sure I set
the fields value. If I forget to modify one statement, things will
break. That's why I'm looking for a declarative way of doing and let
database handle it like it handles setting the default value of a
column on insert.

Chris
--
Chris Velevitch
Manager - Adobe Platform Users Group, Sydney
m: 0415 469 095
www.apugs.org.au

Adobe Platform Users Group, Sydney
September meeting: It's Going To Be Brilliant
Date: Mon 29th September 6pm for 6:30 start
Details and RSVP on http://apugs2008september.eventbrite.com

On Fri, Sep 12, 2008 at 1:53 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Here's a simple example of last modified trigger using plpgsql from way back:

-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
BEGIN
new.lm :=''now'';
RETURN new;
END;
' LANGUAGE 'plpgsql';

This does work in 7.4. It doesn't like 'opaque', whatever that is. It
doesn't like language plpgsql. I'm using a shared hosted database, so
I'm probably not allowed to createlang. And it complains about 'new'.

Chris
--
Chris Velevitch
Manager - Adobe Platform Users Group, Sydney
m: 0415 469 095
www.apugs.org.au

Adobe Platform Users Group, Sydney
September meeting: It's Going To Be Brilliant
Date: Mon 29th September 6pm for 6:30 start
Details and RSVP on http://apugs2008september.eventbrite.com

On Thu, Sep 11, 2008 at 9:10 PM, Chris Velevitch
<chris.velevitch@gmail.com> wrote:

On Fri, Sep 12, 2008 at 1:53 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

Here's a simple example of last modified trigger using plpgsql from way back:

-- FUNCTION --

CREATE FUNCTION modtime () RETURNS opaque AS '
BEGIN
new.lm :=''now'';
RETURN new;
END;
' LANGUAGE 'plpgsql';

This does work in 7.4. It doesn't like 'opaque', whatever that is. It
doesn't like language plpgsql. I'm using a shared hosted database, so
I'm probably not allowed to createlang. And it complains about 'new'.

Do you have a superuser account? Pretty sure you gotta have that to
create lang. OTOH, plpgsql is a "safe" language once installed, so
you should be able to ask your hosting provider to install it. Can't
hurt to ask.

On Thu, Sep 11, 2008 at 8:50 PM, Artacus <artacus@comcast.net> wrote:

How is it that you can declare the default value of a column on insert
but not on update?

You can do this without triggers (at least on 8.3).

UPDATE foo
SET field1 = 2,
field2 = default
WHERE field3 = 22

I just tested it and it will set the value back to the default. The caveat
here is that it won't FORCE the value like it would with a trigger. So while
the trigger would happen automatically, using this approach, you'd have to
remember to also update that field any time you did an update.

Right, but now you've moved the complexity of timestamping updates
into the application layer, where it has to be propagated to all
update queries. Miss one and it won't get updated.

i wonder if you could do it with a rule?

That means I have to then go through all my code and make sure I set
the fields value. If I forget to modify one statement, things will
break.

Right, that's why the right answer for what you want to do is to have a
trigger. I was just giving you an alternative since you didn't like the
trigger answer.

Do you have a superuser account? Pretty sure you gotta have that to
create lang. OTOH, plpgsql is a "safe" language once installed, so
you should be able to ask your hosting provider to install it. Can't
hurt to ask.

For plpgsql I don't believe it actually "installs" anything. The docs
make it sound more like it activates it. So as long as you own your
database you should be able to

createlang plpgsql

Chris Velevitch wrote:

On Thu, Sep 11, 2008 at 6:01 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

you should to use trigger

I've never used trigger before, it looks messy and error prone having
to write functions.

It is, but it's not really all that bad. If you like you can even write
a Pl/PgSQL function that (using EXECUTE) generates the required trigger
functions. Not pretty, but effective.

This would be an ideal case for column triggers that could be included
in DOMAIN types and/or set like column constraints in the table schema
definition. That doesn't seem to be on the cards for PostgreSQL in the
near to mid future, though.

--
Craig Ringer