Re: [GENERAL] Script errors on run

I do believe I did.

I tired with and w/o the DATE word in the EXTRACT statement.

Without the DATE I get:

-----------------------------

airburst=# select usecs_from_date('2008-06-04');

INFO:  good_date =  2008-06-04

ERROR:  function pg_catalog.date_part("unknown", "unknown") is not unique

LINE 1: SELECT  EXTRACT(EPOCH FROM 'good_date')

                ^

HINT:  Could not choose a best candidate function. You may need to add explicit type casts.

QUERY:  SELECT  EXTRACT(EPOCH FROM 'good_date')

CONTEXT:  PL/pgSQL function "usecs_from_date" line 92 at assignment

-----------------------------

Though I do have a cold and I just called my own # thinking I was calling someone else.

I've tried MANY variations and yet I still get this same error.

Please keep sending your suggestions.

(I'm beginning to think this is like programming javascript.  The code is right but the interpreter doesn't think so.)

Thanks all,

Ralph

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

On Jun 4, 2008, at 4:18 PM, GW  wrote:

-----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