| 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@xxxxxxxxxxxxxx on behalf of Ralph Smith
Sent: Wed 6/4/2008 4:04 PM
To: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
|
