Thread: Calculation error

Calculation error

From
Wil Duis
Date:
Hi:
I am new to postgresql (more familiar with Oracle) and am facing an issue I don't understand.
What I am trying to do is the following:

Given a start date/time and a stop date/time I want to calculate
the difference in seconds, using a calc_duration function.

When running this function using another function, run_calc it should
update the duration field in my int_performance_facts table,

But I get error:
WARNING:  Error occurred while executing PL/pgSQL function calc_duration
WARNING:  line 23 at assignment
ERROR:  parser: parse error at or near "$1" at character 20

What am I doing wrong here ?

PS: I am running version 7.3.2 in a SUN/UNIX environment

Thanks in advance:  Wil


Details:
========

my table definition (part of it) is:
------------------------------------

                Table "public.int_performance_facts"
     Column      |          Type          | Modifiers | Description
-----------------+------------------------+-----------+-------------
 duration        | integer                |           |
 start_date      | date                   |           |
 start_date_id   | integer                |           |
 start_time      | time without time zone |           |
 start_time_id   | integer                |           |
 stop_date       | date                   |           |
 stop_date_id    | integer                |           |
 stop_time       | time without time zone |           |
 stop_time_id    | integer                |           |

My calculate function is:
----------------------------
CREATE OR REPLACE FUNCTION calc_duration (date,time,date,time) RETURNS integer AS '
DECLARE

p_start_date ALIAS FOR $1;
p_start_time ALIAS FOR $2;
p_stop_date ALIAS FOR $3;
p_stop_time ALIAS FOR $4;

v_startmoment timestamp;
v_stopmoment  timestamp;
v_epoch_start integer;
v_epoch_stop  integer;

v_duration integer := 0;

BEGIN

v_startmoment := timestamp(p_start_date,p_start_time);
v_epoch_start := date_part(epoch, v_startmoment);

v_stopmoment  := timestamp(p_stop_date,p_stop_time);
v_epoch_start := date_part(epoch, v_stopmoment );

v_duration := v_epoch_stop - v_epoch_start;


RETURN v_duration;

END;
' LANGUAGE 'plpgsql' ;


I am calling this function from:
---------------------------------
CREATE OR REPLACE FUNCTION run_calc () RETURNS integer AS '
DECLARE

 dummy integer ;
 v_query varchar(2000);

BEGIN
  v_query := ''update int_performance_facts
                set duration = calc_duration
                               ( start_date
                               , start_time
                               , stop_date
                               , stop_time
                               );

              '';
   execute v_query;


 RETURN dummy;
END;




Re: Calculation error

From
Tom Lane
Date:
Wil Duis <Wil.Duis@asml.com> writes:
> v_startmoment := timestamp(p_start_date,p_start_time);
> v_epoch_start := date_part(epoch, v_startmoment);
> v_stopmoment  := timestamp(p_stop_date,p_stop_time);
> v_epoch_start := date_part(epoch, v_stopmoment );

The function name "timestamp" needs to be double quoted here to avoid a
syntax conflict with the type declaration syntax TIMESTAMP(n).  But
actually I'd use timestamptz, assuming that your dates and times are in
local time --- the above calculation will give the wrong answers across
a daylight-savings transition.

BTW I think you meant to assign to v_epoch_stop in the last line quoted.
You should also consider making v_epoch_start and v_epoch_stop be float8
not integer, if you want the code to not break in 2038.

>   v_query := ''update int_performance_facts
>                 set duration = calc_duration
>                                ( start_date
>                                , start_time
>                                , stop_date
>                                , stop_time
>                                );
>               '';
>    execute v_query;

Seems like the hard way.  Why not just do the UPDATE directly?

            regards, tom lane

Re: Calculation error

From
chestie
Date:
Wil Duis <Wil.Duis@asml.com> writes:

> Hi:
> I am new to postgresql (more familiar with Oracle) and am facing an issue I don't understand.
> What I am trying to do is the following:

It seems I've have a similar problem to you.

> ERROR:  parser: parse error at or near "$1" at character 20

I kept getting this same error, heres what I was trying to do.

  it := select int4(extract(epoch from timestamp $1 -
           extract(epoch from timestamp $2));

This doesn't work for me, I get the same error message as you.
I'm very new to functions, I don't understand whats going
on, but I did manage to get it working using quote_literal,
but its very ugly.

create or replace function
 subts(timestamp,timestamp)
 returns int as
'
declare
    it int;
    ts1 alias for $1;
    ts2 alias for $2;
    qquery text;
    rec record;
begin
    qquery := ''select int4(extract(epoch from timestamp  ''
    || quote_literal(ts1)
    || ''  ) - extract(epoch from timestamp  ''
    || quote_literal(ts2)
    || '' ))'';

    FOR rec IN EXECUTE qquery LOOP
      it := rec.int4;
    END LOOP;
    return it;
end
'
language 'plpgsql' IMMUTABLE;

The loop is ugly, but I couldn't work out any other way to do it.

Re: Calculation error

From
chestie
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> chestie <mcit@argus.net.au> writes:
>> I kept getting this same error, heres what I was trying to do.
>
>>   it := select int4(extract(epoch from timestamp $1 -
>>            extract(epoch from timestamp $2));
>
> You should just write
>
>   it := select int4(extract(epoch from $1) -
>            extract(epoch from $2));

It works (without the select, my fault).

> You are confusing the syntax

I'm just confused in general. :)
Thanks for the explanation.


Re: Calculation error

From
Tom Lane
Date:
chestie <mcit@argus.net.au> writes:
> I kept getting this same error, heres what I was trying to do.

>   it := select int4(extract(epoch from timestamp $1 -
>            extract(epoch from timestamp $2));

You should just write

  it := select int4(extract(epoch from $1) -
           extract(epoch from $2));

You are confusing the syntax
    timestamp 'string literal'
(or more generally, any type name followed by a string literal) with
something that you should apply to a non-constant value.  That syntax
works *only* for literal constants.

In your example, $1 and $2 are already of type timestamp and require
no further conversion, so the extract()s will work fine as I show above.
If you did need a run-time type conversion, you'd have to write
"CAST($1 AS timestamp)" (the SQL-spec-approved syntax) or
"$1::timestamp" (Postgres' traditional notation).

            regards, tom lane

Re: Calculation error

From
Wil Duis
Date:
Hi Tom
Sorry for my delayed reaction, but I didn't have much time yesterday.

On a trial and error way I tried a lot of variations, but up to now without success;
The closest to succes (I guess) was by using the following syntax:

v_startmoment := ''timestamp(''||p_start_date||'', ''||p_start_time||'')'';
but this resulted in error:
ERROR:  Bad timestamp external representation 'timestamp(2003-05-14, 01:20:40)'

This supprised me since executing from commandline:
   select timestamp'2003-05-10 01:20:40';
works fine!


So, all suggestions are still welcome.

Regards:  Wil


Tom Lane wrote:

>
> The function name "timestamp" needs to be double quoted here to avoid a
> syntax conflict with the type declaration syntax TIMESTAMP(n).  But
> actually I'd use timestamptz, assuming that your dates and times are in
> local time --- the above calculation will give the wrong answers across
> a daylight-savings transition.
>