Re: Unable to create function which takes no arguments

On Mon, Jun 09, 2008 at 12:05:52PM -0400, Michael Eshom wrote:
> I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, which
> will return the current timestamp. However, whenever I try to add this
> function in phpPgAdmin, it says 'Syntax error at or near ")" at
> character 28'.

yes, but the problem is not in this line:

> CREATE FUNCTION unix_timestamp() RETURNS integer AS '

it is in this:

> SELECT current_timestamp()::int4 AS result;

# CREATE FUNCTION unix_timestamp() RETURNS integer AS '
SELECT current_timestamp()::int4 AS result;
' LANGUAGE SQL;
ERROR: syntax error at or near ")"
LINE 2: SELECT current_timestamp()::int4 AS result;
^

what's more, when you fix () issue inside of function it will still be broken:

# CREATE FUNCTION unix_timestamp() RETURNS integer AS 'SELECT current_timestamp::int4 AS result;' LANGUAGE SQL;
ERROR: cannot cast type timestamp with time zone to integer
LINE 1: ...p() RETURNS integer AS 'SELECT current_timestamp::int4 AS re...
^

(it might work in older postgresql versions, i'm not sure).

to make it sane write it that way:

CREATE FUNCTION unix_timestamp() RETURNS integer AS '
SELECT extract(epoch from current_timestamp)::int4;
' LANGUAGE SQL;

depesz