Home > mailing lists

Thread: Script errors on run

Script errors on run

From

Ralph Smith

Date:

04 June 2008, 21:36:23

This is my first 'real' script, one that verifies proper format for a user-entered date string.

Once that is done I want the script to return the UNIX time.

I plan on invoking this script on a psql connection (via .psqlrc), so that I can call it from the command line.

Here's what I have at the end of my script,

************************************

-- ==========================================

good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;

RAISE NOTICE 'good_date = %',good_date ;

Usecs := EXTRACT(EPOCH FROM TIMESTAMP good_date) ;

END ;

Here's what I get when I

*********************

psql -U username dbname -f myfile.sql

CREATE FUNCTION

psql:myfile.sql:119: ERROR: syntax error at or near "$1"

LINE 1: SELECT EXTRACT(EPOCH FROM TIMESTAMP $1 )

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.

Thanks!

Ralph

Re: Script errors on run

From

Stephan Szabo

Date:

04 June 2008, 21:57:28

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

Re: Script errors on run

From

Ralph Smith

Date:

04 June 2008, 22:12:57

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

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

Re: Script errors on run

From

Stephan Szabo

Date:

04 June 2008, 22:30:59

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)

Re: Script errors on run

From

Ralph Smith

Date:

04 June 2008, 23:04:39

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

Re: Script errors on run

From

"Gregory Williamson"

Date:

04 June 2008, 23:18:57

Try doing what was suggested ?

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

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

Re: Script errors on run

From

Ralph Smith

Date:

04 June 2008, 23:28:20

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

Re: Script errors on run

From

Stephan Szabo

Date:

04 June 2008, 23:34:49

On Wed, 4 Jun 2008, Ralph Smith wrote:

>    -- ==========================================
>    good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;
>
>    RAISE INFO 'good_date =  %', good_date ;
>
>    UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ;

You want something like:
 UsecsD := EXTRACT(EPOCH FROM good_date);

Note the lack of single quotes. You want to use the variable's value, not
a literal string with the value 'good_date'.

Re: Script errors on run

From

Ralph Smith

Date:

05 June 2008, 00:06:13

I've tried SO MANY variations of w/ and w/o the apostrophes, but
apparently not just the right one.

As you knew and I doubted, it NOW WORKS!

Whew!
You can bet that I'm keeping this snippet of code handy.

Thank you very much,
Ralph
==================================
On Jun 4, 2008, at 4:34 PM, Stephan Szabo wrote:

> On Wed, 4 Jun 2008, Ralph Smith wrote:
>
>>   -- ==========================================
>>   good_date := to_date(year||'-'||month||'-'||day , 'YYYY-MM-DD') ;
>>
>>   RAISE INFO 'good_date =  %', good_date ;
>>
>>   UsecsD := EXTRACT(EPOCH FROM DATE 'good_date') ;
>
> You want something like:
> UsecsD := EXTRACT(EPOCH FROM good_date);
>
> Note the lack of single quotes. You want to use the variable's
> value, not
> a literal string with the value 'good_date'.