DONE........
thank you very much.
Best Regards,
Alam Surya
----- Original Message -----
From: "Sam Mason" <sam@xxxxxxxxxxxxx>
To: <pgsql-general@xxxxxxxxxxxxxx>
Sent: Monday, November 10, 2008 18:50
Subject: Re: 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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
