Thread: new type proposal

I've been thinking on some new pesudo-types like SERIAL.

1) LAST_MODIFIED is the timestamp of that last time this row was
modified.  Easy enough to do currently with triggers.
2) TIME_CREATED is the timestamp of when this row was first created with
an INSERT.  I'm not sure how to do this because it needs to be read
only.

Both of these could be useful to me and I can implement them by myself
(except #2 which I'll just have to trust myself not to screw up) but
general users might find them useful.

What do you think?

At 03:43 PM 2/6/2001 -0500, Joseph Shraibman wrote:
>I've been thinking on some new pesudo-types like SERIAL.
>
>1) LAST_MODIFIED is the timestamp of that last time this row was
>modified.  Easy enough to do currently with triggers.
>2) TIME_CREATED is the timestamp of when this row was first created with
>an INSERT.  I'm not sure how to do this because it needs to be read
>only.


MS sql server has a TIMESTAMP field which acts exactly like LAST_MODIFIED
type you proposed. I find this field very handy when attempting to
synchronize data. I would welcome such a field type in postgres.

BTW is the currency datatype working with access and ODBC yet?


:wq
Tim Uckun
Due Diligence Inc.  http://www.diligence.com/    Americas Background
Investigation Expert.
If your company isn't doing background checks, maybe you haven't considered
the risks of a bad hire.

> I've been thinking on some new pesudo-types like SERIAL.
>
> 1) LAST_MODIFIED is the timestamp of that last time this row was
> modified.  Easy enough to do currently with triggers.
> 2) TIME_CREATED is the timestamp of when this row was first created with
> an INSERT.  I'm not sure how to do this because it needs to be read
> only.
>
> Both of these could be useful to me and I can implement them by myself
> (except #2 which I'll just have to trust myself not to screw up) but
> general users might find them useful.


Well, SERIAL does auto-sequence create and DEFAULT.   I don't see why we
can't add these features like we did for SERIAL.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

> > (except #2 which I'll just have to trust myself not to screw up) but
> > general users might find them useful.
>
>
> Well, SERIAL does auto-sequence create and DEFAULT.   I don't see why we
> can't add these features like we did for SERIAL.

Feeping creaturism?

I don't think this stuff belongs in postgres core...

-alex

I think these are vital!  I was looking for something like this a few months
ago and was frustrated that I had to create a trigger to accomplish this...
I know, I'm lazy.  But if it were built in, I think it would be a big bonus!

-Dan

: I've been thinking on some new pesudo-types like SERIAL.
:
: 1) LAST_MODIFIED is the timestamp of that last time this row was
: modified.  Easy enough to do currently with triggers.
: 2) TIME_CREATED is the timestamp of when this row was first created with
: an INSERT.  I'm not sure how to do this because it needs to be read
: only.
:
: Both of these could be useful to me and I can implement them by myself
: (except #2 which I'll just have to trust myself not to screw up) but
: general users might find them useful.
:
: What do you think?

El Mar 06 Feb 2001 18:54, Dan Wilson escribió:
> I think these are vital!  I was looking for something like this a few
> months ago and was frustrated that I had to create a trigger to accomplish
> this... I know, I'm lazy.  But if it were built in, I think it would be a
> big bonus!

Not at all. It would be a step back. What your asking for already exists, and
any ANSI-SQL book will tell you to do it with a trigger.
So wake up, don't be lazy, and CREATE those TRIGGERS!!!

Saludos... ;-)

--
System Administration: It's a dirty job,
but someone told I had to do it.
-----------------------------------------------------------------
Martín Marqués            email:     martin@math.unl.edu.ar
Santa Fe - Argentina        http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

OK... if this is the case, would a situation like this be a good example to
have on the PG website in a generic form? Then, those of us who are just
learning SQL and Postgresql can know how to do this correctly.

When I was playing with PHP, I always appreciated how their documentation had
the ability for people to add examples and comments to the documentation. I
often learned more from these than the real docs.

:)

Thanks,
Sam


>===== Original Message =====
>El Mar 06 Feb 2001 18:54, Dan Wilson escribió:
>> I think these are vital!  I was looking for something like this a few
>> months ago and was frustrated that I had to create a trigger to accomplish
>> this... I know, I'm lazy.  But if it were built in, I think it would be a
>> big bonus!
>
>"Martin A. Marques" <martin@math.unl.edu.ar>:
>Not at all. It would be a step back. What your asking for already exists, and
>any ANSI-SQL book will tell you to do it with a trigger.
>So wake up, don't be lazy, and CREATE those TRIGGERS!!!
>
>Saludos... ;-)
>

: El Mar 06 Feb 2001 18:54, Dan Wilson escribió:
: > I think these are vital!  I was looking for something like this a few
: > months ago and was frustrated that I had to create a trigger to
accomplish
: > this... I know, I'm lazy.  But if it were built in, I think it would be
a
: > big bonus!
:
: Not at all. It would be a step back. What your asking for already exists,
and
: any ANSI-SQL book will tell you to do it with a trigger.
: So wake up, don't be lazy, and CREATE those TRIGGERS!!!
:
: Saludos... ;-)

I disagree!

What would this do that would be non-standard?  Does the SERIAL datatype add
something that is not standard?  No... it just allows for an easy way to
implement something that is standard.  The SERIAL "type" isn't really a
datatype, it's just a keyword that allows you to automatically specify an
int4 column with a related sequence and default.  I don't see why the same
thing couldn't be done with TIMESTAMP!

I'm not saying to create an actual datatype that is called TIMESTAMP or
LAST_MODIFIED, just use it in a create script.  It would then be implemented
with the DATE datatype combined with triggers.

Makes perfect sense to me!

-Dan

BTW: I'm completely awake and I build applications specifically so I don't
have to do things by hand (ie. so I can be "lazy" or more efficient,
whichever you prefer).

On Tue, 6 Feb 2001, Dan Wilson wrote:

> What would this do that would be non-standard?  Does the SERIAL datatype add
> something that is not standard?  No... it just allows for an easy way to
> implement something that is standard.  The SERIAL "type" isn't really a
> datatype, it's just a keyword that allows you to automatically specify an
> int4 column with a related sequence and default.  I don't see why the same
> thing couldn't be done with TIMESTAMP!
Such way the madnesssH^H^H^Hmysql lies ;)

I firmly believe that people who need that feature should implement it
themselves via triggers, and rest of us shouldn't suffer from the code
bloat resulting to support this.

SERIAL datatype is different, as something like that is supported by every
RDBMS, and pretty much everyone takes [or can take] a use of that...

> I'm not saying to create an actual datatype that is called TIMESTAMP or
> LAST_MODIFIED, just use it in a create script.  It would then be implemented
> with the DATE datatype combined with triggers.
>
> Makes perfect sense to me!
>
> -Dan
>
> BTW: I'm completely awake and I build applications specifically so I don't
> have to do things by hand (ie. so I can be "lazy" or more efficient,
> whichever you prefer).
>
>

El Mar 06 Feb 2001 19:26, Sam Snow escribió:
> OK... if this is the case, would a situation like this be a good example to
> have on the PG website in a generic form? Then, those of us who are just
> learning SQL and Postgresql can know how to do this correctly.
>
> When I was playing with PHP, I always appreciated how their documentation
> had the ability for people to add examples and comments to the
> documentation. I often learned more from these than the real docs.

Totally with you on this one!!!! :-)
I think THAT would be of great help, especially for SQL newbies.

Saludos... :-)

--
System Administration: It's a dirty job,
but someone told I had to do it.
-----------------------------------------------------------------
Martín Marqués            email:     martin@math.unl.edu.ar
Santa Fe - Argentina        http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Tim Uckun wrote:
>
> At 03:43 PM 2/6/2001 -0500, Joseph Shraibman wrote:
> >I've been thinking on some new pesudo-types like SERIAL.
> >
> >1) LAST_MODIFIED is the timestamp of that last time this row was
> >modified.  Easy enough to do currently with triggers.
> >2) TIME_CREATED is the timestamp of when this row was first created with
> >an INSERT.  I'm not sure how to do this because it needs to be read
> >only.
>
> MS sql server has a TIMESTAMP field which acts exactly like LAST_MODIFIED
> type you proposed. I find this field very handy when attempting to
> synchronize data. I would welcome such a field type in postgres.
>

There already is something called timestamp, and I thought it was a sql
standard type.

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

El Mar 06 Feb 2001 19:38, Dan Wilson escribió:
>
> What would this do that would be non-standard?  Does the SERIAL datatype
> add something that is not standard?  No... it just allows for an easy way
> to implement something that is standard.  The SERIAL "type" isn't really a
> datatype, it's just a keyword that allows you to automatically specify an
> int4 column with a related sequence and default.  I don't see why the same
> thing couldn't be done with TIMESTAMP!

You're right about the SERIAL type, but I still don't think there should be a
new type that those what a trigger can do.
If the developers added a new data type for each user that is to lazy to
create a trigger, this would look much like MySQL. :-)
Any way, with this kind of thoughts, why don't we take away the triggers, and
just make another built-in data type each time a user wants it?

> I'm not saying to create an actual datatype that is called TIMESTAMP or
> LAST_MODIFIED, just use it in a create script.  It would then be
> implemented with the DATE datatype combined with triggers.
>
> Makes perfect sense to me!

But it would be built-in as it was said before? In that case we would have a
bigger backend. Very bad.... :-(

> BTW: I'm completely awake and I build applications specifically so I don't
> have to do things by hand (ie. so I can be "lazy" or more efficient,
> whichever you prefer).

You spotted the lazy stuff!!! ;-)

Saludos... :-)

--
System Administration: It's a dirty job,
but someone told I had to do it.
-----------------------------------------------------------------
Martín Marqués            email:     martin@math.unl.edu.ar
Santa Fe - Argentina        http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Alex Pilosov wrote:
>
> On Tue, 6 Feb 2001, Dan Wilson wrote:
>
> > What would this do that would be non-standard?  Does the SERIAL datatype add
> > something that is not standard?  No... it just allows for an easy way to
> > implement something that is standard.  The SERIAL "type" isn't really a
> > datatype, it's just a keyword that allows you to automatically specify an
> > int4 column with a related sequence and default.  I don't see why the same
> > thing couldn't be done with TIMESTAMP!
> Such way the madnesssH^H^H^Hmysql lies ;)
>
> I firmly believe that people who need that feature should implement it
> themselves via triggers, and rest of us shouldn't suffer from the code
> bloat resulting to support this.

I noticed that people are ignoring the time created part of my
proposal.  How can a read only field be implemented?  A trigger that
causes and error if that field is updated?

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio.  http://www.targabot.com

On Tuesday 06 February 2001 16:09, Joseph Shraibman wrote:
> Alex Pilosov wrote:
> > On Tue, 6 Feb 2001, Dan Wilson wrote:
> > > What would this do that would be non-standard?  Does the SERIAL
> > > datatype add something that is not standard?  No... it just allows for
> > > an easy way to implement something that is standard.  The SERIAL "type"
> > > isn't really a datatype, it's just a keyword that allows you to
> > > automatically specify an int4 column with a related sequence and
> > > default.  I don't see why the same thing couldn't be done with
> > > TIMESTAMP!
> >
> > Such way the madnesssH^H^H^Hmysql lies ;)
> >
> > I firmly believe that people who need that feature should implement it
> > themselves via triggers, and rest of us shouldn't suffer from the code
> > bloat resulting to support this.
>
> I noticed that people are ignoring the time created part of my
> proposal.  How can a read only field be implemented?  A trigger that
> causes and error if that field is updated?

Just don't write to the field.

: El Mar 06 Feb 2001 19:38, Dan Wilson escribió:
: >
: > What would this do that would be non-standard?  Does the SERIAL datatype
: > add something that is not standard?  No... it just allows for an easy
way
: > to implement something that is standard.  The SERIAL "type" isn't really
a
: > datatype, it's just a keyword that allows you to automatically specify
an
: > int4 column with a related sequence and default.  I don't see why the
same
: > thing couldn't be done with TIMESTAMP!
:
: You're right about the SERIAL type, but I still don't think there should
be a
: new type that those what a trigger can do.
: If the developers added a new data type for each user that is to lazy to
: create a trigger, this would look much like MySQL. :-)
: Any way, with this kind of thoughts, why don't we take away the triggers,
and
: just make another built-in data type each time a user wants it?

No.... if this required a new datatype, then I would agree with you, but it
doesn't. It's just an automagic way to impliment existing functionality.  No
additional datatype is needed and it wouldn't replace what a trigger does.
We would use a trigger to impliment it.

When the parser comes across a create table statment and the AUTO_TIMESTAMP
(or whatever) keyword, it would then realize that the user wants an actual
timestamp datatype and would then automatically create a trigger to update
that column whenever the tuple was changed.

So here's your create statement:

CREATE TABLE auto_time_stamp_tbl (
   table_id SERIAL,
   mod_time AUTO_TIMESTAMP,
   my_data varchar(30)
);

This would then generate a table like the following:

CREATE TABLE "auto_time_stamp_tbl" (
   "table_id" int4 DEFAULT nextval('auto_time_stamp_tb_table_id_seq'::text)
NOT NULL,
   "mod_time" timestamp NOT NULL,
   "my_data" varchar(30) NOT NULL,
   CONSTRAINT "auto_time_stamp_tbl_pkey" PRIMARY KEY ("table_id")
);

And would have a trigger associated with it and a function to update the
mod_time column.

: > I'm not saying to create an actual datatype that is called TIMESTAMP or
: > LAST_MODIFIED, just use it in a create script.  It would then be
: > implemented with the DATE datatype combined with triggers.
: >
: > Makes perfect sense to me!
:
: But it would be built-in as it was said before? In that case we would have
a
: bigger backend. Very bad.... :-(

It can't be much more that much more overhead, and it would only be run
during a create table statement.  All the other code to support this is
already in the backend.

Alex mentioned that not many people need it, but I think if it existed more
people would use something like this.  I usually handle it through my
application code, which is what I think a lot of people do just because they
don't want to bother with creating the trigger.

Oh well... I will leave it up to those that know postgres best.  They can
make the decision, but I think it would be a great added feature without
adding anything non-standard.

-Dan

On Tue, 6 Feb 2001, Joseph Shraibman wrote:

> Alex Pilosov wrote:
> >
> > On Tue, 6 Feb 2001, Dan Wilson wrote:
> >
> > > What would this do that would be non-standard?  Does the SERIAL datatype add
> > > something that is not standard?  No... it just allows for an easy way to
> > > implement something that is standard.  The SERIAL "type" isn't really a
> > > datatype, it's just a keyword that allows you to automatically specify an
> > > int4 column with a related sequence and default.  I don't see why the same
> > > thing couldn't be done with TIMESTAMP!
> > Such way the madnesssH^H^H^Hmysql lies ;)
> >
> > I firmly believe that people who need that feature should implement it
> > themselves via triggers, and rest of us shouldn't suffer from the code
> > bloat resulting to support this.
>
> I noticed that people are ignoring the time created part of my
> proposal.  How can a read only field be implemented?  A trigger that
> causes and error if that field is updated?

That'd be one way of doing it, if the value is modified to
something distinct raise an exception...

'begin
 if (NEW.b!=OLD.b) then
  RAISE EXCEPTION ''...''';
 end if;
 return NEW;
end;'

>
> > MS sql server has a TIMESTAMP field which acts exactly like LAST_MODIFIED
> > type you proposed. I find this field very handy when attempting to
> > synchronize data. I would welcome such a field type in postgres.
> >
>
>There already is something called timestamp, and I thought it was a sql
>standard type.

MS SQL server has a different terminology I think. They use DATETIME to
indicate the equavalent of a postgres TIMESTAMP. In sql server timestamp is
a read only type that is set by the server. Anytime the row is updated or
on insert it puts in a timestamp.

Some people have indicated that perhaps this does not belong in the core
because it's easily achieved with triggers and I think they have a point
but maybe what's really needed are domains. Not just your average every day
domains but supercool domains with triggers!.

that way you can define a domain called UPDATED using a timestamp field and
a insert or an update trigger perhaps even a default value or a check. This
would make it easier to insert the same rules and triggers into every table
just by adding a field with the defined domain.

Interbase  support domains which let you define checks and defaults but not
triggers. I know this kind of grandiose but it would be cool.
----------------------------------------------
              Tim Uckun
       Mobile Intelligence Unit.
----------------------------------------------
    "There are some who call me TIM?"
----------------------------------------------

"Martin A. Marques" wrote:
>
> El Mar 06 Feb 2001 19:26, Sam Snow escribió:
> > OK... if this is the case, would a situation like this be a good example to
> > have on the PG website in a generic form? Then, those of us who are just
> > learning SQL and Postgresql can know how to do this correctly.
> >
> > When I was playing with PHP, I always appreciated how their documentation
> > had the ability for people to add examples and comments to the
> > documentation. I often learned more from these than the real docs.
>
> Totally with you on this one!!!! :-)
> I think THAT would be of great help, especially for SQL newbies.

Maybe what we need is some sort of module system for these things so you
could do something like:

USE MODULE auto_timestamp ON foo (last_modified)

which could apply the rules/triggers to the relevant table/column. Two
big pluses I can think of:

1. It lets lots of non-developers contribute (e.g. me) without hacking C.
2. You are using standard language features so you can learn from
modules or cut and paste if you loathe them.

I don't know how complex module support would be, but CPAN doesn't seem
to have done perl any harm.

- Richard Huxton

Replying to my own post as I think things through.

----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>


> Maybe what we need is some sort of module system for these things so you
> could do something like:
>
> USE MODULE auto_timestamp ON foo (last_modified)
>
> which could apply the rules/triggers to the relevant table/column. Two
> big pluses I can think of:
>
> 1. It lets lots of non-developers contribute (e.g. me) without hacking C.
> 2. You are using standard language features so you can learn from
> modules or cut and paste if you loathe them.
>
> I don't know how complex module support would be, but CPAN doesn't seem
> to have done perl any harm.
>
> - Richard Huxton

Actually - we've pretty much got this in 7.1 anyway.
Plpgsql gives us EXECUTE <text> so we can generate triggers etc on the fly.
\i lets us parse a file

So - you could:

-- import functions from module
\i auto_timestamp.pgmod
-- add the triggers
auto_timestamp_addto(mytable,mycolumn)
-- remove the functions provided by the module
auto_timestamp_remove_module()

Does this make sense, or am I talking rubbish here?

- Richard Huxton

Greetings, Joseph!

At 07.02.2001, 13:13, you wrote:
JS> I noticed that people are ignoring the time created part of my
JS> proposal.  How can a read only field be implemented?  A trigger that
JS> causes and error if that field is updated?
Well, why not just do something like
new.time_created_field := old.time_created_field;
in a BEFORE UPDATE trigger?..


--
Yours, Alexey V. Borzov, Webmaster of RDW

El Mar 06 Feb 2001 20:58, Dan Wilson escribió:
>
> : new type that those what a trigger can do.
> : If the developers added a new data type for each user that is to lazy to
> : create a trigger, this would look much like MySQL. :-)
> : Any way, with this kind of thoughts, why don't we take away the triggers,
>
> and
>
> : just make another built-in data type each time a user wants it?
>
> No.... if this required a new datatype, then I would agree with you, but it
> doesn't. It's just an automagic way to impliment existing functionality.
> No additional datatype is needed and it wouldn't replace what a trigger
> does. We would use a trigger to impliment it.

OK, but anyway, that function (which can be made by anyone) would be on the
template0(1) database? Then why don't you create it there, and you'll have it
for all the databases you built in the future.

> When the parser comes across a create table statment and the AUTO_TIMESTAMP
> (or whatever) keyword, it would then realize that the user wants an actual
> timestamp datatype and would then automatically create a trigger to update
> that column whenever the tuple was changed.
>
> So here's your create statement:
>
> CREATE TABLE auto_time_stamp_tbl (
>    table_id SERIAL,
>    mod_time AUTO_TIMESTAMP,
>    my_data varchar(30)
> );
>
> This would then generate a table like the following:
>
> CREATE TABLE "auto_time_stamp_tbl" (
>    "table_id" int4 DEFAULT nextval('auto_time_stamp_tb_table_id_seq'::text)
> NOT NULL,
>    "mod_time" timestamp NOT NULL,
>    "my_data" varchar(30) NOT NULL,
>    CONSTRAINT "auto_time_stamp_tbl_pkey" PRIMARY KEY ("table_id")
> );

It isn't that bad what you want, but I insist with not make a "TO BIG"
backend, or start putting things by default in the template database, if it
is that the users will not normally use it.

> And would have a trigger associated with it and a function to update the
> mod_time column.
>
> : But it would be built-in as it was said before? In that case we would
> : have
>
> a
>
> : bigger backend. Very bad.... :-(
>
> It can't be much more that much more overhead, and it would only be run
> during a create table statement.  All the other code to support this is
> already in the backend.

Well, it may not be my case, but what about people that have scripts that
constantlly built new tables.
Any way, yuo can add it to your template database.

> Alex mentioned that not many people need it, but I think if it existed more
> people would use something like this.  I usually handle it through my
> application code, which is what I think a lot of people do just because
> they don't want to bother with creating the trigger.

Bad idea.

> Oh well... I will leave it up to those that know postgres best.  They can
> make the decision, but I think it would be a great added feature without
> adding anything non-standard.

The decision is on the developers, not me. ;-)

Saludos... :-)

--
System Administration: It's a dirty job,
but someone told I had to do it.
-----------------------------------------------------------------
Martín Marqués            email:     martin@math.unl.edu.ar
Santa Fe - Argentina        http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------