Re: 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=#