[GENERAL] Updating column on row update

[Thom Brown]

Hi,

This should be simple, but for some reason I'm not quite sure what the solution is.  I want to be able to update the value of a column for rows that have been updated.  More specifically, if a row is updated, I want it's modified_date column to be populated with the current time stamp.  I've looked at triggers and rules, and it looks like I'd need to create a function just to achieve this which seems incredibly clumsy and unnecessary.  Could someone enlighten me?

Thanks

Thom

[Thom Brown]

2009/11/22 Aaron Burnett <abur...@bzzagent.com>

this is how I do it if this helps:

column_name timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) without time zone

Hi Aaron.  Thanks for the reply, but that would only insert the current date upon insertion into the table, not when the row is updated.

For example

CREATE TABLE timetest(

id SERIAL NOT NULL,

stuff text,

stamp timestamp NOT NULL DEFAULT now()

);

INSERT INTO timetest (stuff) VALUES ('meow');

The table would contain:

 id | stuff |           stamp            

----+-------+----------------------------

  1 | meow  | 2009-11-22 20:04:51.261739

But then I'd execute:

UPDATE timetest SET stuff = 'bark' WHERE id = 1;

 id | stuff |           stamp            

----+-------+----------------------------

  1 | bark  | 2009-11-22 20:04:51.261739

You can see the time hasn't changed.  But I'd want that stamp column to update to the current time without referring to that column in the update statement.

Thanks

Thom

[Aaron Burnett]

this is how I do it if this helps:

column_name timestamp without time zone NOT NULL DEFAULT ('now'::text)::timestamp(6) without time zone

[Scott Marlowe]

Well, you DO have to create a function, but it's not all that clumsy
really. Also it's quite flexible so you can do lots of complex stuff
and hide it away in a trigger function.

Example:

-- 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

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

[Adrian Klaver]

You will need to use an UPDATE trigger with associated function.

--
Adrian Klaver
akl...@comcast.net

[Thom Brown]

2009/11/22 Scott Marlowe <scott....@gmail.com>

Thanks Scott.  It's a shame a function has to be used because it then has the dependency of plpgsql being loaded.  I'm attempting to write a database schema to accompany a PostgreSQL driver for a popular CMS, but I guess I could get it to load plpgsql in as a language.

The problem now is if the the schema creation script is run against a database where the language is already installed, I would get an error saying it already exists.  Is there a way to get it to check for it first, and only create it if it isn't exist?  Bear in mind I'd want this to be compatible at least as far back as 8.1.

Thanks

Thom

[Scott Marlowe]

Try this:

select * from pg_language ;

Pretty sure that exists pretty far back.

[Scott Marlowe]

On Sun, Nov 22, 2009 at 1:32 PM, Thom Brown <thom...@gmail.com> wrote:
> Thanks Scott.  It's a shame a function has to be used because it then has
> the dependency of plpgsql being loaded.  I'm attempting to write a database
> schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
> could get it to load plpgsql in as a language.

Nice thing about plpgsql is that as long as you own the database you
don't have to be a superuser to create language plgpsql.

[Thom Brown]

2009/11/22 Scott Marlowe <scott....@gmail.com>

> Thanks Scott.  It's a shame a function has to be used because it then has
> the dependency of plpgsql being loaded.  I'm attempting to write a database
> schema to accompany a PostgreSQL driver for a popular CMS, but I guess I
> could get it to load plpgsql in as a language.
> The problem now is if the the schema creation script is run against a
> database where the language is already installed, I would get an error
> saying it already exists.  Is there a way to get it to check for it first,
> and only create it if it isn't exist?  Bear in mind I'd want this to be
> compatible at least as far back as 8.1.

Try this:

select * from pg_language ;

Pretty sure that exists pretty far back.

Yes, I noticed that existed in the catalogs, but how could that be incorporated into an installation SQL script?  The language constructs I imagine I'd need to test that are in plpgsql itself.

Thanks

Thom

[Adrian Klaver]

As far as I know the language exists ERROR will not stop the rest of the process
from completing. You may in search of solution to a problem that does not
exist :)

--
Adrian Klaver
akl...@comcast.net

[Christophe Pettus]

David Fetter and Andreas Scherbaum also have solutions for this in
reployment scripts:

http://people.planetpostgresql.org/dfetter/index.php?/archives/23-CREATE-OR-REPLACE-LANGUAGE.html
http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html
--
-- Christophe Pettus
x...@thebuild.com

[Thom Brown]

2009/11/22 Christophe Pettus <x...@thebuild.com>

David Fetter and Andreas Scherbaum also have solutions for this in reployment scripts:

       http://people.planetpostgresql.org/dfetter/index.php?/archives/23-CREATE-OR-REPLACE-LANGUAGE.html
       http://andreas.scherbaum.la/blog/archives/346-create-language-if-not-exist.html
--
-- Christophe Pettus
  x...@thebuild.com

Ah, I think that will work.  Thanks :)

Thom 

[Tom Lane]

Craig Ringer <cr...@postnewspapers.com.au> writes:
> I do think this comes up often enough that a built-in trigger "update
> named column with result of expression on insert" trigger might be
> desirable.

There's something of the sort in contrib already, I believe, though
it's so old it still uses abstime :-(

> So might "CREATE LANGUAGE ... IF NOT EXISTS". Maybe even "CREATE ROLE
> ... IF NOT EXISTS" and "CREATE USER ... IF NOT EXISTS" - I know I'd find
> them really handy.

CREATE IF NOT EXISTS has been proposed and rejected before, more than
once. Please see the archives.

regards, tom lane

[Tom Lane]

Thom Brown <thom...@gmail.com> writes:
> As for having plpgsql installed by default, are there any security
> implications?

Well, that's pretty much exactly the question --- are there? It would
certainly make it easier for someone to exploit any other security
weakness they might find. I believe plain SQL plus SQL functions is
Turing-complete, but that doesn't mean it's easy or fast to write loops
etc in it.

[Hannu Krosing]

On Tue, 2009-11-24 at 09:46 +0000, Thom Brown wrote:
> 2009/11/24 Hannu Krosing <ha...@2ndquadrant.com>

> On Sun, 2009-11-22 at 18:51 -0500, Tom Lane wrote:
> > Craig Ringer <cr...@postnewspapers.com.au> writes:
> > > I do think this comes up often enough that a built-in
> trigger "update
> > > named column with result of expression on insert" trigger
> might be
> > > desirable.
> >
> > There's something of the sort in contrib already, I believe,
> though
> > it's so old it still uses abstime :-(
>
>

> What's wrong with abstime ?
>
> it is valid for timestamps up to 2038-01-19 and it's on-disk
> size
> smaller than other timestamp options
>
>
> But it's very very deprecated and could be removed at any time. It's
> been so for years now, and I wouldn't want to *start* using something
> which is deprecated.
>
> Thom

I'd expect it to have an afterlife as a separately maintained type
somewhere for those who care about data sizes, similar other space
savers like ip4 type.

--
Hannu Krosing http://www.2ndQuadrant.com
PostgreSQL Scalability and Availability
Services, Consulting and Training

[Tom Lane]

"Kevin Grittner" <Kevin.G...@wicourts.gov> writes:
> So we're conceding that this is a valid need and people will now have
> a way to meet it. Is the argument against having CINE syntax that it
> would be more prone to error than the above, or that the code would be
> so large and complex as to create a maintenance burden?

The argument against CINE is that it's unsafe. The fragment proposed
by Andrew is no safer, of course, but it could be made safe by adding
additional checks that the properties of the existing object are what
the script expects. So in principle that's an acceptable approach,
whereas CINE will never be safe.

But actually I thought we had more or less concluded that CREATE OR
REPLACE LANGUAGE would be acceptable (perhaps only if it's given
without any extra args?). Or for that matter there seems to be enough
opinion on the side of just installing plpgsql by default. CINE is
a markedly inferior alternative to either of those.