Re: Script errors on run

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" 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).

=============================================

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(at)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=#