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