[SQL] 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 = 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
2010/5/18 Richard Broersma : > On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
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 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 > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
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 : > > On Tue, May 18, 2010 at 12:08 PM, Kenneth Marshall 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 > > > -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] plperlu user function.
Greetings,
I am trying to write a user function on the server to retrive image
files. Currently I wrote the following:
CREATE OR REPLACE FUNCTION perl_getfile(text)
RETURNS OID AS
$BODY$
my $tmpfile = shift;
open my $IFHAND, '<', $tmpfile
or elog(ERROR, qq{could not open the file "$tmpfile": $!});
binmode $IFHAND;
my $result = '';
while(my $buffer = <$IFHAND>)
{
$result .= $buffer;
}
return $result;
$BODY$
LANGUAGE 'plperlu';
on the client I do (PHP sniplet):
$result = pg_query($dbcnx, "SELECT
perl_getfile('/home/harel/Misc/At_work.jpg')");
and I get an error:
Warning: pg_query() [function.pg-query
]: Query failed: ERROR:
invalid input syntax for type oid: "ÿØÿà" in
/home/harel/Prj/php/testImages/mysqlImage1/image.php
Any recommendation how to do it right?
--
Regards.
David Harel,
==
Home office +972 77 7657645
Cellular: +972 54 4534502
Snail Mail: Amuka
D.N Merom Hagalil
13802
Israel
Email: harel...@ergolight-sw.com
Re: [SQL] plperlu user function.
On Tue, May 18, 2010 at 15:29, David Harel wrote: > Greetings, > > I am trying to write a user function on the server to retrive image files. > Currently I wrote the following: > CREATE OR REPLACE FUNCTION perl_getfile(text) > RETURNS OID AS I think you meant returns bytea or text or something... else -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
Kenneth Marshall 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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How to get CURRENT_DATE in a pl/pgSQL function
On 19/05/2010, Tim Landscheidt wrote: > Kenneth Marshall 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/ -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] plperlu user function.
On 05/19/10 01:29, David Harel wrote: I am trying to write a user function on the server to retrive image files. Currently I wrote the following: my $tmpfile = shift; open my $IFHAND, '<', $tmpfile Any recommendation how to do it right? first of all, stop opening files at all, return to a client a pathname, then give to Caesar what is Caesar's, and to God what is God's. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
