Thread: declare column update expression
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 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
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 > > > 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 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
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]
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]
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]
[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
>
>
>
> 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
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