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