Re: Get interval in months - Mailing list pgsql-general

From Sam Mason
Subject Re: Get interval in months
Date
Msg-id 20081110115017.GA2459@frubble.xen.chris-lamb.co.uk
Whole thread Raw
In response to Re: Get interval in months  ("dbalinglung" <alamsurya@centrin.net.id>)
List pgsql-general
On Mon, Nov 10, 2008 at 12:06:04PM +0700, dbalinglung wrote:
> Dear Expert,
>
> I have a function to getting time interval bellow :
>
> create or replace function scmaster.pr_gettimeinterval(time without time zone, time without time zone, numeric(5,2))
returnschar(10) As ' 
> declare v_timein    alias for $1;
>         v_timeout   alias for $2;
>         v_timebreak alias for $3;
>         v_output    char(10);
> begin
>   raise notice ''-- BOF --'';
>   v_output := select ((v_timeout - v_timein) - interval ''v_timebreak minutes'');

You've got the brackets wrong here, you need brackets around the whole
SELECT statement a bit like subselects.  Also, the INTERVAL literal
is wrong.  At the moment, you're telling PG to interpret the string
'v_timebreak minutes' as an interval which will fail.  You can either
concatenate the numeric value of the "v_timebreak" column with the
string ' minutes' to get a valid string that can be interpreted as an
INTERVAL; or a better option would be to create a fixed interval and
then multiply it by your numeric value.

>
>   raise notice ''-- EOF --'';
> return v_output;
> end;'
> language plpgsql;
>
>
> and when i compilled from pgAdmin, i got some error message

I'd probably write it like this:

  CREATE OR REPLACE FUNCTION cmaster.pr_gettimeinterval(
      _timein TIME, _timeout TIME, _timebreak NUMERIC)
    RETURNS TEXT LANGUAGE plpgsql AS
  $$
    DECLARE
      _output TEXT;
    BEGIN
      _output := (SELECT _timeout - _timein - INTERVAL '1 minute' * _timebreak);
      RETURN _output;
    END
  $$;

The operator precedence is such that this will work without brackets,
but you can put them in if you want.  The "_output" variable is
also unneeded, you can just RETURN the SELECT statement in one line
(i.e. RETURN (SELECT 1) works), but I left it in because I thought you
may want to do other things with it.


  Sam

pgsql-general by date:

Previous
From: "Andrus"
Date:
Subject: Optimizing IN queries
Next
From: Sam Mason
Date:
Subject: Re: Fulltext index