Re: Calculation error

Wil Duis <Wil(dot)Duis(at)asml(dot)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.