Calculation error

From: Wil Duis <Wil(dot)Duis(at)asml(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Calculation error
Date: 2003-06-02 08:47:13
Message-ID: 3EDB0F10.AE25B3C8@asml.nl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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;

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Herbie McDuck 2003-06-02 08:52:35 Re: pg_ctl?
Previous Message Nabil Sayegh 2003-06-02 08:11:23 Re: pg_ctl?