Thread: Triggers - need help !!!
Hi, I am writing some triggers for my project. I am writing a trigger on a table after delete....I want to know how u refer to the row that is being deleted. For example for a trigger function after INSERT/UPDATE u refer to the row being inserted/updated using NEW like this.... CREATE FUNCTION ..... BEGINIF UPDATE(CreateTime) THEN SELECT INTO ValidCount COUNT(*) FROM PointTable WHERE NEW.PointId = PointTable.PointId;ENDIF; .......... END ; ' LANGUAGE 'plpgsql'; My question is how will refer to the row being deleted in the trigger function.\ I want to do something like this DELETE PointTable FROM PointTable WHERE PointTable.PointId = DELETED.PointId; Pls help me with this. > With Best Regards > Pradeep Kumar P J >
On 2004-07-07 08:45, Użytkownik Pradeepkumar, Pyatalo (IE10) napisał: > Hi, > > I am writing some triggers for my project. > I am writing a trigger on a table after delete....I want to know how u refer > to the row that is being deleted. > > For example for a trigger function after INSERT/UPDATE u refer to the row > being inserted/updated using NEW like this.... Use OLD instead of NEW. You can also use OLD inside UPDATE triggers - you have access to row before update. Regards, Tomasz Myrta
Thanks a lot for ur help.In the trigger, I am checking if a field is updated or not. The syntax I use is IF UPDATE(CreateTime) THEN........ END IF; Is this syntax correct. -----Original Message----- From: Tomasz Myrta [mailto:jasiek@klaster.net] Sent: Wednesday, July 07, 2004 12:50 PM To: Pradeepkumar, Pyatalo (IE10) Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Triggers - need help !!! On 2004-07-07 08:45, Użytkownik Pradeepkumar, Pyatalo (IE10) napisał: > Hi, > > I am writing some triggers for my project. > I am writing a trigger on a table after delete....I want to know how u refer > to the row that is being deleted. > > For example for a trigger function after INSERT/UPDATE u refer to the row > being inserted/updated using NEW like this.... Use OLD instead of NEW. You can also use OLD inside UPDATE triggers - you have access to row before update. Regards, Tomasz Myrta
Pradeepkumar, Pyatalo (IE10) wrote: > Thanks a lot for ur help. > In the trigger, I am checking if a field is updated or not. The syntax I > use is > > IF UPDATE(CreateTime) THEN > .... > .... > END IF; > > Is this syntax correct. No, and I don't recall seeing anything like it in the manuals. IF OLD.CreateTime <> NEW.CreateTime THEN ... -- Richard Huxton Archonet Ltd
I'd like to add that a NULL value might mess things up. If CreateTime may be null, try this: if (OLD.CreateTime <> NEW.CreateTime) OR (OLD.CreateTime ISNULL <> NEW.CreateTime ISNULL) THEN ... or this: if COALESCE(OLD.CreateTime, '3001-01-01') <> COALESCE(NEW.CreateTime, '3001-01-01') THEN ... (provided you can safely assume that createtimes remain in this millenium ;) ) or maybe: if COALESCE(OLD.CreateTime <> NEW.CreateTime, OLD.CreateTime ISNULL <> NEW.CreateTime ISNULL) THEN ... However; I'd stay with the first one. It's quite simple and Y3K-safe ;) Also, it seems to be the most effective of them, if any. G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Richard Huxton" <dev@archonet.com> Sent: Wednesday, July 07, 2004 1:03 PM > Pradeepkumar, Pyatalo (IE10) wrote: > > Thanks a lot for ur help. > > In the trigger, I am checking if a field is updated or not. The syntax I > > use is > > > > IF UPDATE(CreateTime) THEN > > .... > > .... > > END IF; > > > > Is this syntax correct. > > No, and I don't recall seeing anything like it in the manuals. > > IF OLD.CreateTime <> NEW.CreateTime THEN > ...
Hello all, First of all, accept my apologies for what is surely a dumb question, and yes i have been reading extensively through all the documents, but i really need to ask this.. :) I have recently started the migration of a large ex-MySql database to postgresql, and im still "adapting" to the new tweaks of this new (to me) environment. My question is basically... how does postgresql deal with the equivalent of "permanent connections of mysql"? Alongside with the database, i have an extensive amount of .c code that used to just reuse sockets if they were already in an open state (and only if needed new one(s) would be open). It's an application that will run as a standalone, but many times per minute, so the reusage is indeed a must for me. I have substituted the "mysql_ping"s with PQconnectPolls just to see if the behaviour would be alike, and right now that seems to work, but i'm in a standstill regarding the sockets and permanent connection usage. Any help/directions someone might give me will be deeply appreciated. Regards, \\pb
"Richard Huxton" <dev@archonet.com> wrote in message news:40EBD891.3050205@archonet.com... > Pradeepkumar, Pyatalo (IE10) wrote: > > IF UPDATE(CreateTime) THEN > > .... > > .... > > END IF; > > > > Is this syntax correct. > > No, and I don't recall seeing anything like it in the manuals. ... and it's always an excellent time to read them ... > IF OLD.CreateTime <> NEW.CreateTime THEN > ... ... being mindful of the case where CreateTime might be NULL ...
Hello, I'm using a TIMESTAMP column with a now() default which (correctly i assume) uses a 'yyyy-mm-dd hh:mm:ss' format. Is it possible to make it something like 'yyyy-mm-dd hh:mm:ss:cc' ? (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and not .ccccc as the TIME format. Thanks, \\pb
On Aug 3, 2004, at 4:22 PM, Pedro B. wrote: > Is it possible to make it something like 'yyyy-mm-dd hh:mm:ss:cc' ? > (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and > not .ccccc as the TIME format. timestamp and timestamptz both take an optional precision parameter. What you want is timestamp(2) or timestamptz(2), I believe. Michael Glaesemann grzm myrealbox com
Hi, On Tue, 3 Aug 2004, Pedro B. wrote: > I'm using a TIMESTAMP column with a now() default which (correctly i > assume) uses a 'yyyy-mm-dd hh:mm:ss' format. > > Is it possible to make it something like 'yyyy-mm-dd hh:mm:ss:cc' ? > (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and > not .ccccc as the TIME format. Is this what you are asking? test=> SELECT now()::timestamp(1); now ------------------------2004-08-03 13:58:48.60 (1 row) Regards, -- Devrim GUNDUZ devrim~gunduz.org devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org
O kyrios Pedro B. egrapse stis Aug 3, 2004 : > Hello, > > I'm using a TIMESTAMP column with a now() default which (correctly i > assume) uses a 'yyyy-mm-dd hh:mm:ss' format. Wrong!, timestamp does not use any human readable format to be stored. Its not like MS* tools where dates/times are actually text. now() returns the current UNIX (your running UNIX right?) timestamp which in turn is measured in seconds,miliseconds since the epoch. i.e. 1970-01-01 00:00:00 > > Is it possible to make it something like 'yyyy-mm-dd hh:mm:ss:cc' ? > (basically, a DATE and a TIME, but with 2 decimal cases on the :cc and > not .ccccc as the TIME format. > > Thanks, > \\pb > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > -- -Achilleus
On Aug 3, 2004, at 7:23 PM, Achilleus Mantzios wrote: > now() returns the current UNIX (your running UNIX right?) timestamp > which > in turn is > measured in seconds,miliseconds since the epoch. > i.e. 1970-01-01 00:00:00 I believe this is incorrect. I believe PostgreSQL uses its own timestamp datatype internally (which is, indeed, not as text in an easy-to-read form). On my machine (running cvs-head), test=# select now(); now ------------------------------- 2004-08-03 20:27:18.822646+09 (1 row) which is definitely not seconds.milliseconds since epoch. You can use extract to get seconds.milliseconds from epoch, but I don't think this is how it's stored internally. test=# select extract(epoch from now()); date_part ----------------- 1091532506.3222 (1 row) Just some additional trivia, current_timestamp is an SQL-spec compliant alias for now(), and might be a better choice if one is concerned with compatibility. Michael Glaesemann grzm myrealbox com
O kyrios Michael Glaesemann egrapse stis Aug 3, 2004 : > > On Aug 3, 2004, at 7:23 PM, Achilleus Mantzios wrote: > > > now() returns the current UNIX (your running UNIX right?) timestamp > > which > > in turn is > > measured in seconds,miliseconds since the epoch. > > i.e. 1970-01-01 00:00:00 > > I believe this is incorrect. I believe PostgreSQL uses its own Do you suggest postgresql has any other means of getting time except the time(2) syscall?? > timestamp datatype internally (which is, indeed, not as text in an > easy-to-read form). On my machine (running cvs-head), > > test=# select now(); > now > ------------------------------- > 2004-08-03 20:27:18.822646+09 > (1 row) > > which is definitely not seconds.milliseconds since epoch. You can use > extract to get seconds.milliseconds from epoch, but I don't think this > is how it's stored internally. > > test=# select extract(epoch from now()); > date_part > ----------------- > 1091532506.3222 > (1 row) > > Just some additional trivia, current_timestamp is an SQL-spec compliant > alias for now(), and might be a better choice if one is concerned with > compatibility. > > Michael Glaesemann > grzm myrealbox com > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > -- -Achilleus
On Aug 3, 2004, at 8:50 PM, Achilleus Mantzios wrote: > O kyrios Michael Glaesemann egrapse stis Aug 3, 2004 : >> >> I believe this is incorrect. I believe PostgreSQL uses its own > > Do you suggest postgresql has any other means of getting > time except the time(2) syscall?? > >> timestamp datatype internally (which is, indeed, not as text in an >> easy-to-read form). On my machine (running cvs-head), No. I'm just saying that PostgreSQL does not represent or store timestamps as epoch timestamps internally. I don't know for sure how PostgreSQL gets the current timestamp, and I wouldn't be surprised if it was via the time(2) syscall. Michael Glaesemann grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
> No. I'm just saying that PostgreSQL does not represent or store
> timestamps as epoch timestamps internally.
You're wrong.
It's not exactly Unix-like because we use a different epoch date
(2000-1-1 not 1970-1-1) but the concept is just the same: what's
stored is the number of seconds before or after the epoch. The
default is to store this as a double precision number (hence supporting
fractional seconds, with a machine-dependent amount of precision)
but you can compile the server to use 64-bit integers instead. In that
case the integer value actually represents microseconds before or after
the epoch, and so the precision is fixed at microseconds.
What you see when you display the value is an external textual
representation, not the internal form. This is generally true for
all Postgres datatypes except text/varchar/char ...
regards, tom lane
On Aug 4, 2004, at 12:13 AM, Tom Lane wrote: > Michael Glaesemann <grzm@myrealbox.com> writes: >> No. I'm just saying that PostgreSQL does not represent or store >> timestamps as epoch timestamps internally. > > You're wrong. > > It's not exactly Unix-like because we use a different epoch date > (2000-1-1 not 1970-1-1) but the concept is just the same: what's > stored is the number of seconds before or after the epoch. The > default is to store this as a double precision number (hence supporting > fractional seconds, with a machine-dependent amount of precision) > but you can compile the server to use 64-bit integers instead. In that > case the integer value actually represents microseconds before or after > the epoch, and so the precision is fixed at microseconds. As I understood Achilleus, he said that PostgreSQL used UNIX epoch timestamp internally, which is defined as seconds from 1970-01-01. What I said is that PostgreSQL does not use UNIX epoch internally, which is exactly what you've verified. PostgreSQL uses seconds and microseconds from 2000-01-01, and PostgreSQL can be compiled to use 64-bit integers (rather than double precision floats) to represent integer microseconds from 2000-01-01. Thank you for explaining these things. However, I don't quite understand how I am wrong in saying that PostgreSQL does not use UNIX epoch timestamps internally, as you've clearly explained it doesn't. > What you see when you display the value is an external textual > representation, not the internal form. Which I don't think was ever at issue. Thanks again for explaining the internals. I'm trying to learn as much as I can grepping the source, but it's often easier to hear an explanation. Michael Glaesemann grzm myrealbox com
Michael Glaesemann <grzm@myrealbox.com> writes:
> I don't quite understand how I am wrong in saying that PostgreSQL does
> not use UNIX epoch timestamps internally, as you've clearly explained
> it doesn't.
We are talking at cross-purposes. I thought you were suggesting that PG
doesn't use a seconds-from-epoch form at all, but some other format
(such as perhaps separate yyyy/mm/dd/hh/mm/ss fields). Sorry if I added
to the confusion instead of dispelling it.
regards, tom lane
On Aug 4, 2004, at 7:55 AM, Tom Lane wrote: > Sorry if I added to the confusion instead of dispelling it. Not at all. I had no idea how timestamps are stored internally, but I do now. I just knew it wasn't UNIX epoch or the same as the text representation displayed in results. Your explanation has turned my negative "what it is not" knowledge into the much more positive "what it is", and that's always a good thing. Michael Glaesemann grzm myrealbox com