Обсуждение: How to get CURRENT_DATE in a pl/pgSQL function
I am trying to write a function that updates the date column to the current date. According to: http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT you can use CURRENT_DATE. When I try to use it in the following pl/pgSQL function it gives the error: ERROR: date/time value "current" is no longer supported CONTEXT: PL/pgSQL function "merge_data" line 4 at assignment Here is the code I am using: CREATE FUNCTION merge_data(key INT, i INT) RETURNS VOID AS $$ DECLARE curtime date; BEGIN curtime := 'CURRENT_DATE'; LOOP -- first try to update the key UPDATE data SET count = i, date = curtimeWHERE k = key; IF found THEN RETURN; END IF; -- not there, so try to insert the key -- if someone else inserts the same key concurrently, -- we could get a unique-key failure BEGIN INSERT INTO data(k, count, date) VALUES (key, i, curtime); RETURN; EXCEPTION WHEN unique_violation THEN -- do nothing, and loop to try the UPDATE again END; END LOOP; END; $$ LANGUAGE plpgsql; It looks like it is keying on the CURRENT and dropping the _DATE piece. I suspect that I am doing something wrong but I am not able to find a pointer in the docs or mailing lists. Any help would be greatly appreciated. Regards, Ken
On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall <ktm@rice.edu> wrote: > http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT > > you can use CURRENT_DATE. When I try to use it in > the following pl/pgSQL function it gives the error: > BEGIN > curtime := 'CURRENT_DATE'; > LOOP I'm not "up" on my pl/pgSQL, but isn't CURRENT_DATE a literal value so it shouldn't to be enclosed in single quotes? Another idea would be to: CAST( now() AS DATE ) -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
2010/5/18 Richard Broersma <richard.broersma@gmail.com>: > On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall <ktm@rice.edu> wrote: > >> http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT >> >> you can use CURRENT_DATE. When I try to use it in >> the following pl/pgSQL function it gives the error: > >> BEGIN >> curtime := 'CURRENT_DATE'; >> LOOP > > > I'm not "up" on my pl/pgSQL, but isn't CURRENT_DATE a literal value so > it shouldn't to be enclosed in single quotes? no - it is mutable constant postgres=# CREATE OR REPLACE FUNCTION fo() RETURNS date AS $$ DECLARE d date; BEGIN d := CURRENT_DATE; RETURN d; END; $$ LANGUAGE plpgsql; CREATE FUNCTION Time: 450.665 ms postgres=# select fo(); fo ────────────2010-05-18 (1 row) Regards Pavel Stehule > > Another idea would be to: CAST( now() AS DATE ) > > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
To follow-up, It works using 'now' and I assume that since curtime is of type DATE that the assignment casts the return automatically to type DATE. Thank you for the ideas. Regards, Ken On Tue, May 18, 2010 at 12:12:46PM -0700, Richard Broersma wrote: > On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall <ktm@rice.edu> wrote: > > > http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT > > > > you can use CURRENT_DATE. When I try to use it in > > the following pl/pgSQL function it gives the error: > > > BEGIN > > ? ?curtime := 'CURRENT_DATE'; > > ? ?LOOP > > > I'm not "up" on my pl/pgSQL, but isn't CURRENT_DATE a literal value so > it shouldn't to be enclosed in single quotes? > > Another idea would be to: CAST( now() AS DATE ) > > > > -- > Regards, > Richard Broersma Jr. > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > http://pugs.postgresql.org/lapug >
Okay, this works as well. Thank you for all of the assistance. Regards, Ken On Tue, May 18, 2010 at 09:25:00PM +0200, Pavel Stehule wrote: > 2010/5/18 Richard Broersma <richard.broersma@gmail.com>: > > On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall <ktm@rice.edu> wrote: > > > >> http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT > >> > >> you can use CURRENT_DATE. When I try to use it in > >> the following pl/pgSQL function it gives the error: > > > >> BEGIN > >> ?? ??curtime := 'CURRENT_DATE'; > >> ?? ??LOOP > > > > > > I'm not "up" on my pl/pgSQL, but isn't CURRENT_DATE a literal value so > > it shouldn't to be enclosed in single quotes? > > no - it is mutable constant > > postgres=# > CREATE OR REPLACE FUNCTION fo() > RETURNS date AS $$ > DECLARE d date; > BEGIN > d := CURRENT_DATE; > RETURN d; > END; > $$ LANGUAGE plpgsql; > CREATE FUNCTION > Time: 450.665 ms > postgres=# select fo(); > fo > ???????????????????????????????????? > 2010-05-18 > (1 row) > > > Regards > Pavel Stehule > > > > > Another idea would be to: ??CAST( now() AS DATE ) > > > > > > > > -- > > Regards, > > Richard Broersma Jr. > > > > Visit the Los Angeles PostgreSQL Users Group (LAPUG) > > http://pugs.postgresql.org/lapug > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > >
Kenneth Marshall <ktm@rice.edu> wrote: > It works using 'now' and I assume that since curtime is > of type DATE that the assignment casts the return automatically > to type DATE. Thank you for the ideas. > [...] What's wrong with Pavel's correct and to-the-point answer? Tim
On 19/05/2010, Tim Landscheidt <tim@tim-landscheidt.de> wrote: > Kenneth Marshall <ktm@rice.edu> wrote: > >> It works using 'now' and I assume that since curtime is >> of type DATE that the assignment casts the return automatically >> to type DATE. Thank you for the ideas. >> [...] > > What's wrong with Pavel's correct and to-the-point answer? No need actually to cast... just use current_date without the quotes. Its not a string. > > Tim > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > -- Brian Modra Land line: +27 23 5411 462 Mobile: +27 79 69 77 082 5 Jan Louw Str, Prince Albert, 6930 Postal: P.O. Box 2, Prince Albert 6930 South Africa http://www.zwartberg.com/
On 05/18/10 23:27, Kenneth Marshall wrote: > It works using 'now' and I assume that since curtime is now() is NOT the CURRENT timestamp in fact, it is about the timestamp of the current transaction has been started. it is the really USEFUL value, still u have to remember this meaning.
Brian Modra <brian@zwartberg.com> wrote: >>> It works using 'now' and I assume that since curtime is >>> of type DATE that the assignment casts the return automatically >>> to type DATE. Thank you for the ideas. >>> [...] >> What's wrong with Pavel's correct and to-the-point answer? > No need actually to cast... just use current_date without the quotes. > Its not a string. Where did Pavel suggest to cast or use a string? Tim
On 2010-05-18, Kenneth Marshall <ktm@rice.edu> wrote: > I am trying to write a function that updates the > date column to the current date. According to: > > http://www.postgresql.org/docs/8.4/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT > > you can use CURRENT_DATE. When I try to use it in > the following pl/pgSQL function it gives the error: > > ERROR: date/time value "current" is no longer supported > CONTEXT: PL/pgSQL function "merge_data" line 4 at assignment > > Here is the code I am using: > > CREATE FUNCTION merge_data(key INT, i INT) RETURNS > VOID AS > $$ > DECLARE > curtime date; > BEGIN > curtime := 'CURRENT_DATE'; use one of CURRENT_DATE 'today' NOW() CURRENT_TIMESTAMP 'now' there are probably others which will work too.
On Wed, May 19, 2010 at 12:26:07PM +0400, silly sad wrote: > On 05/18/10 23:27, Kenneth Marshall wrote: > >> It works using 'now' and I assume that since curtime is > > now() is NOT the CURRENT timestamp in fact, > it is about the timestamp of the current transaction has been started. > > it is the really USEFUL value, still u have to remember this meaning. > That is a good point. My main concern was locking the value to to point when the pl/pgSQL function was called initially because of the prepare process. Any of these will work if assigned to a variable and not included directly in the SQL statement. Cheers, Ken