Thread: Calculation error
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;
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
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.
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.
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
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.
>