Re: Get interval in months

DONE........

thank you very much.


Best Regards,


Alam Surya


----- Original Message -----
From: "Sam Mason" <sam@samason.me.uk>
To: <pgsql-general@postgresql.org>
Sent: Monday, November 10, 2008 18:50
Subject: Re: [GENERAL] Get interval in months


> 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)) returns char(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
>