Re: Calculation error

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.