pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get CURRENT_DATE in a pl/pgSQL function
11+ messages / 7 participants
[nested] [flat]
* How to get CURRENT_DATE in a pl/pgSQL function
@ 2010-05-18 19:08 Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
2010-05-19 11:25 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 11+ messages in thread
From: Kenneth Marshall @ 2010-05-18 19:08 UTC (permalink / raw)
To: pgsql-sql
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 = curtime WHERE 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
@ 2010-05-18 19:12 ` Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:25 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Pavel Stehule <pavel.stehule@gmail.com>
2010-05-18 19:27 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
1 sibling, 2 replies; 11+ messages in thread
From: Richard Broersma @ 2010-05-18 19:12 UTC (permalink / raw)
To: Kenneth Marshall <ktm@rice.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
@ 2010-05-18 19:25 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-05-18 19:28 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
1 sibling, 1 reply; 11+ messages in thread
From: Pavel Stehule @ 2010-05-18 19:25 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; +Cc: Kenneth Marshall <ktm@rice.edu>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:25 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Pavel Stehule <pavel.stehule@gmail.com>
@ 2010-05-18 19:28 ` Kenneth Marshall <ktm@rice.edu>
0 siblings, 0 replies; 11+ messages in thread
From: Kenneth Marshall @ 2010-05-18 19:28 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Richard Broersma <richard.broersma@gmail.com>; pgsql-sql
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
> >
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
@ 2010-05-18 19:27 ` Kenneth Marshall <ktm@rice.edu>
2010-05-18 22:20 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-19 08:26 ` Re: How to get CURRENT_DATE in a pl/pgSQL function silly sad <sad@bankir.ru>
1 sibling, 2 replies; 11+ messages in thread
From: Kenneth Marshall @ 2010-05-18 19:27 UTC (permalink / raw)
To: Richard Broersma <richard.broersma@gmail.com>; +Cc: 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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:27 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
@ 2010-05-18 22:20 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-19 03:48 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Brian Modra <brian@zwartberg.com>
1 sibling, 1 reply; 11+ messages in thread
From: Tim Landscheidt @ 2010-05-18 22:20 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:27 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 22:20 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Tim Landscheidt <tim@tim-landscheidt.de>
@ 2010-05-19 03:48 ` Brian Modra <brian@zwartberg.com>
2010-05-19 11:18 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 1 reply; 11+ messages in thread
From: Brian Modra @ 2010-05-19 03:48 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:27 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 22:20 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-19 03:48 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Brian Modra <brian@zwartberg.com>
@ 2010-05-19 11:18 ` Tim Landscheidt <tim@tim-landscheidt.de>
0 siblings, 0 replies; 11+ messages in thread
From: Tim Landscheidt @ 2010-05-19 11:18 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:27 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
@ 2010-05-19 08:26 ` silly sad <sad@bankir.ru>
2010-05-19 12:27 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
1 sibling, 1 reply; 11+ messages in thread
From: silly sad @ 2010-05-19 08:26 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:27 ` Re: How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-19 08:26 ` Re: How to get CURRENT_DATE in a pl/pgSQL function silly sad <sad@bankir.ru>
@ 2010-05-19 12:27 ` Kenneth Marshall <ktm@rice.edu>
0 siblings, 0 replies; 11+ messages in thread
From: Kenneth Marshall @ 2010-05-19 12:27 UTC (permalink / raw)
To: silly sad <sad@bankir.ru>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: How to get CURRENT_DATE in a pl/pgSQL function
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
@ 2010-05-19 11:25 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 11+ messages in thread
From: Jasen Betts @ 2010-05-19 11:25 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-05-19 12:27 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-05-18 19:08 How to get CURRENT_DATE in a pl/pgSQL function Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:12 ` Richard Broersma <richard.broersma@gmail.com>
2010-05-18 19:25 ` Pavel Stehule <pavel.stehule@gmail.com>
2010-05-18 19:28 ` Kenneth Marshall <ktm@rice.edu>
2010-05-18 19:27 ` Kenneth Marshall <ktm@rice.edu>
2010-05-18 22:20 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-19 03:48 ` Brian Modra <brian@zwartberg.com>
2010-05-19 11:18 ` Tim Landscheidt <tim@tim-landscheidt.de>
2010-05-19 08:26 ` silly sad <sad@bankir.ru>
2010-05-19 12:27 ` Kenneth Marshall <ktm@rice.edu>
2010-05-19 11:25 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox