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