Re: Get interval in months
От | dbalinglung |
---|---|
Тема | Re: Get interval in months |
Дата | |
Msg-id | 2BF538D91A954B419727426A18DEEE1C@alam обсуждение исходный текст |
Ответ на | Re: Get interval in months ("dbalinglung" <alamsurya@centrin.net.id>) |
Список | pgsql-general |
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 >
В списке pgsql-general по дате отправления: