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