Re: Script errors on run - Mailing list pgsql-general
| From | Ralph Smith |
|---|---|
| Subject | Re: Script errors on run |
| Date | |
| Msg-id | 517E419C-16D8-4448-93E8-E3D5A40D163D@washington.edu Whole thread Raw |
| In response to | Re: Script errors on run (Stephan Szabo <sszabo@megazone.bigpanda.com>) |
| Responses |
Re: Script errors on run
(Stephan Szabo <sszabo@megazone.bigpanda.com>)
|
| List | pgsql-general |
On Jun 4, 2008, at 2:56 PM, Stephan Szabo wrote:
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).
=============================================
Well I cleaned things up a bit, but I'm still getting stuck on that EXTRACT command:
The following is the script in file: library_date.sql
CREATE OR REPLACE FUNCTION usecs_from_date(given_date varchar) RETURNS int AS
$$
/* given_date Must be of the format 'YYYY-MM-DD', however single digit
months and days are allowed (are handled here).
If a single digit year is used then this function will assume 200X as the year.
If a double digit year is used then numbers >= 70 are assumed to be 19XX.
Three digit years are not allowed.
*/
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 timestamp ;
UsecsD double precision ;
Usecs int ;
BEGIN
-- CLEANSING CODE HERE
-- ==========================================
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 ;
UsecsD := EXTRACT(EPOCH FROM TIMESTAMP 'good_date') ;
Usecs := CAST(UsecsD AS INT) ;
RETURN Usecs ;
END ;
$$ LANGUAGE plpgsql ;
*****************************
This is 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: date_string = 2008-06-04
INFO: good_date = 2008-06-04 00:00:00
ERROR: invalid input syntax for type timestamp: "good_date"
CONTEXT: SQL statement "SELECT EXTRACT(EPOCH FROM TIMESTAMP 'good_date')"
PL/pgSQL function "usecs_from_date" line 96 at assignment
airburst=#
pgsql-general by date: