Search Postgresql Archives

Re: Script errors on run

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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@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=# 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux