Calculation error - Mailing list pgsql-novice

From Wil Duis
Subject Calculation error
Date
Msg-id 3EDB0F10.AE25B3C8@asml.nl
Whole thread Raw
Responses Re: Calculation error
Re: Calculation error
List pgsql-novice
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;




pgsql-novice by date:

Previous
From: Nabil Sayegh
Date:
Subject: Re: pg_ctl?
Next
From: Herbie McDuck
Date:
Subject: Re: pg_ctl?