Обсуждение: Script errors on run
On Wed, 4 Jun 2008, Ralph Smith wrote: > -- ========================================== > good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ; > RAISE NOTICE 'good_date = %',good_date ; > Usecs := EXTRACT(EPOCH FROM TIMESTAMP good_date) ; > END ; > > QUERY: SELECT EXTRACT(EPOCH FROM TIMESTAMP $1 ) > CONTEXT: SQL statement in PL/PgSQL function "usecs_from_date" near > line 92 > > Is this not a programmable extraction??? > I'm missing something here. TIMESTAMP '...' describes a timestamp literal. If you wanted to explicitly cast the value in good_date as a timestamp, you'd probably want CAST(good_date AS TIMESTAMP). If good_date is of type date, however, I believe the cast to timestamp is implicit, so you should probably be able to just use extract(epoch from good_date).
On Wed, 4 Jun 2008, Ralph Smith wrote:-- ==========================================good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;RAISE NOTICE 'good_date = %',good_date ;Usecs := EXTRACT(EPOCH FROM TIMESTAMP good_date) ;END ;QUERY: SELECT EXTRACT(EPOCH FROM TIMESTAMP $1 )CONTEXT: SQL statement in PL/PgSQL function "usecs_from_date" nearline 92Is this not a programmable extraction???I'm missing something here.
=============================
TIMESTAMP '...' describes a timestamp literal.
If you wanted to explicitly cast the value in good_date as a timestamp,
you'd probably want CAST(good_date AS TIMESTAMP).
If good_date is of type date, however, I believe the cast to timestamp is
implicit, so you should probably be able to just use extract(epoch from
good_date).
On Wed, 4 Jun 2008, Ralph Smith wrote: > date_string := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ; > RAISE INFO 'date_string = %', date_string ; > good_date := to_timestamp(date_string, 'YYYY-MM-DD') ; > RAISE INFO 'good_date = %', good_date ; This seems like alot of extra work, due to the implicit cast from date to timestamp. I think good_date := to_date(year || '-' || month || '-' || day, 'YYYY-MM-DD') might work and just be simpler. > UsecsD := EXTRACT(EPOCH FROM TIMESTAMP 'good_date') ; If good_date's already a timestamp, I think this should just be: EXTRACT(EPOCH FROM good_date)
Same problem, see below -------------------------------- On Jun 4, 2008, at 3:30 PM, Stephan Szabo wrote: > On Wed, 4 Jun 2008, Ralph Smith wrote: > >> date_string := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ; >> RAISE INFO 'date_string = %', date_string ; >> good_date := to_timestamp(date_string, 'YYYY-MM-DD') ; >> RAISE INFO 'good_date = %', good_date ; > > This seems like alot of extra work, due to the implicit cast from > date to > timestamp. I think > good_date := to_date(year || '-' || month || '-' || day, 'YYYY-MM-DD') > might work and just be simpler. > >> UsecsD := EXTRACT(EPOCH FROM TIMESTAMP 'good_date') ; > > If good_date's already a timestamp, I think this should just be: > EXTRACT(EPOCH FROM good_date) ========================================= ************************* The code: DECLARE year varchar ; month varchar ; day varchar ; pslash1 int ; pslash2 int ; year_len int ; month_len int ; day_len int ; date_string varchar ; good_date date ; UsecsD double precision ; Usecs int ; BEGIN -- My cleansing code here -- ========================================== good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ; RAISE INFO 'good_date = %', good_date ; UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ; Usecs := CAST(UsecsD AS INT) ; RETURN Usecs ; END ; $$ LANGUAGE plpgsql ; - ************************* Here's what I'm getting now: smithrn@flexo:~/PL-SQL$ psql -U airburst airburst Welcome to psql 8.2.6, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit airburst=# \i misc.sql \i library_date.sql CREATE FUNCTION CREATE FUNCTION airburst=# select usecs_from_date('2008-06-04'); INFO: good_date = 2008-06-04 ERROR: invalid input syntax for type date: "good_date" CONTEXT: SQL statement "SELECT EXTRACT(EPOCH FROM DATE 'good_date')" PL/pgSQL function "usecs_from_date" line 92 at assignment airburst=#
Try doing what was suggested ?
Greg Williamson
Senior DBA
DigitalGlobe
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Ralph Smith
Sent: Wed 6/4/2008 4:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Script errors on run
Same problem, see below
--------------------------------
On Jun 4, 2008, at 3:30 PM, Stephan Szabo wrote:
> On Wed, 4 Jun 2008, Ralph Smith wrote:
>
>> date_string := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;
>> RAISE INFO 'date_string = %', date_string ;
>> good_date := to_timestamp(date_string, 'YYYY-MM-DD') ;
>> RAISE INFO 'good_date = %', good_date ;
>
> This seems like alot of extra work, due to the implicit cast from
> date to
> timestamp. I think
> good_date := to_date(year || '-' || month || '-' || day, 'YYYY-MM-DD')
> might work and just be simpler.
>
>> UsecsD := EXTRACT(EPOCH FROM TIMESTAMP 'good_date') ;
>
> If good_date's already a timestamp, I think this should just be:
> EXTRACT(EPOCH FROM good_date)
=========================================
*************************
The code:
DECLARE
year varchar ;
month varchar ;
day varchar ;
pslash1 int ;
pslash2 int ;
year_len int ;
month_len int ;
day_len int ;
date_string varchar ;
good_date date ;
UsecsD double precision ;
Usecs int ;
BEGIN
-- My cleansing code here
-- ==========================================
good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;
RAISE INFO 'good_date = %', good_date ;
UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ;
Usecs := CAST(UsecsD AS INT) ;
RETURN Usecs ;
END ;
$$ LANGUAGE plpgsql ;
-
*************************
Here's what I'm getting now:
smithrn@flexo:~/PL-SQL$ psql -U airburst airburst
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
airburst=# \i misc.sql \i library_date.sql
CREATE FUNCTION
CREATE FUNCTION
airburst=# select usecs_from_date('2008-06-04');
INFO: good_date = 2008-06-04
ERROR: invalid input syntax for type date: "good_date"
CONTEXT: SQL statement "SELECT EXTRACT(EPOCH FROM DATE 'good_date')"
PL/pgSQL function "usecs_from_date" line 92 at assignment
airburst=#
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----Original Message-----
From: pgsql-general-owner@postgresql.org on behalf of Ralph Smith
Sent: Wed 6/4/2008 4:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Script errors on run
Same problem, see below
--------------------------------
On Jun 4, 2008, at 3:30 PM, Stephan Szabo wrote:
> On Wed, 4 Jun 2008, Ralph Smith wrote:
>
>> date_string := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;
>> RAISE INFO 'date_string = %', date_string ;
>> good_date := to_timestamp(date_string, 'YYYY-MM-DD') ;
>> RAISE INFO 'good_date = %', good_date ;
>
> This seems like alot of extra work, due to the implicit cast from
> date to
> timestamp. I think
> good_date := to_date(year || '-' || month || '-' || day, 'YYYY-MM-DD')
> might work and just be simpler.
>
>> UsecsD := EXTRACT(EPOCH FROM TIMESTAMP 'good_date') ;
>
> If good_date's already a timestamp, I think this should just be:
> EXTRACT(EPOCH FROM good_date)
=========================================
*************************
The code:
DECLARE
year varchar ;
month varchar ;
day varchar ;
pslash1 int ;
pslash2 int ;
year_len int ;
month_len int ;
day_len int ;
date_string varchar ;
good_date date ;
UsecsD double precision ;
Usecs int ;
BEGIN
-- My cleansing code here
-- ==========================================
good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;
RAISE INFO 'good_date = %', good_date ;
UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ;
Usecs := CAST(UsecsD AS INT) ;
RETURN Usecs ;
END ;
$$ LANGUAGE plpgsql ;
-
*************************
Here's what I'm getting now:
smithrn@flexo:~/PL-SQL$ psql -U airburst airburst
Welcome to psql 8.2.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
airburst=# \i misc.sql \i library_date.sql
CREATE FUNCTION
CREATE FUNCTION
airburst=# select usecs_from_date('2008-06-04');
INFO: good_date = 2008-06-04
ERROR: invalid input syntax for type date: "good_date"
CONTEXT: SQL statement "SELECT EXTRACT(EPOCH FROM DATE 'good_date')"
PL/pgSQL function "usecs_from_date" line 92 at assignment
airburst=#
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, 4 Jun 2008, Ralph Smith wrote: > -- ========================================== > good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ; > > RAISE INFO 'good_date = %', good_date ; > > UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ; You want something like: UsecsD := EXTRACT(EPOCH FROM good_date); Note the lack of single quotes. You want to use the variable's value, not a literal string with the value 'good_date'.
I've tried SO MANY variations of w/ and w/o the apostrophes, but apparently not just the right one. As you knew and I doubted, it NOW WORKS! Whew! You can bet that I'm keeping this snippet of code handy. Thank you very much, Ralph ================================== On Jun 4, 2008, at 4:34 PM, Stephan Szabo wrote: > On Wed, 4 Jun 2008, Ralph Smith wrote: > >> -- ========================================== >> good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ; >> >> RAISE INFO 'good_date = %', good_date ; >> >> UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ; > > You want something like: > UsecsD := EXTRACT(EPOCH FROM good_date); > > Note the lack of single quotes. You want to use the variable's > value, not > a literal string with the value 'good_date'.