Re: Plpgsql Question

Oxeye wrote:

> I'm new to Postgresql and plpgslq. I wrote a plpgsql to return epoch time
> from a table, but had problem running it. The error returned:
>
> NOTICE: Error occurred while executing PL/pgSQL function sleeptime
> NOTICE: line 10 at assignment
> ERROR: Bad timestamp external representation 'rec_runtime.runtime'
>
> My plpgsql function:
>
> create function sleeptime () returns float as '
> declare
> rec_runtime record;
> ret_sleepsecs float;
> begin
> select into rec_runtime runtime from mon_nextrun order by runtime
> limit 1;
> if rec_runtime.runtime is null
> then
> return 60;
> end if;
> ret_sleepsecs := extract (epoch from timestamp
> ''rec_runtime.runtime'') as float;
> return ret_sleepsecs;
> end;
> ' language 'plpgsql';
>

Just a bit of overkill :). You can easily do that without resorting to a
procedural language.

If you prefer your queries functional looking:

SELECT coalesce( date_part('epoch', runtime ), 60)::float
FROM mon_nextrun

Or you can use the more 'SQLish' (and verbose) bare word look:

SELECT CAST ( CASE WHEN runtime IS NULL THEN 60
ELSE EXTRACT (epoch FROM runtime) END
AS FLOAT ) AS sleeptime
FROM mon_nextrun

If you want it so you get a scalar from sleeptime() just wrap either of
those in an sql function.

CREATE OR REPLACE FUNCTION sleeptime() RETURNS FLOAT AS '
<query goes here>
' LANGUAGE SQL;

If you insert the first in, remeber to escape the single quotes. You could
also make it a more general wrapper and take runtime as an argument.

References:
http://www.postgresql.org/idocs/index.php?functions-conditional.html
http://www.postgresql.org/idocs/index.php?functions-datetime.html
http://www.postgresql.org/idocs/index.php?xfunc.html