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: