Thread: Unable to create function which takes no arguments

<font size="-1"><font face="Arial">I am a project manager for a popular forum system. We are adding support for
PostgreSQLin the next version (which is currently in beta), and have added several PostgreSQL functions to emulate
MySQLfunctions of the same name.<br /><br /> I am trying to create a "UNIX_TIMESTAMP()" function in PostgreSQL, which
willreturn the current timestamp. However, whenever I try to add this function in phpPgAdmin, it says 'Syntax error at
ornear ")" at character 28'.<br /><br /> This is the SQL I'm using:<br /><br /></font></font> <pre>CREATE FUNCTION
unix_timestamp()RETURNS integer AS 'SELECT current_timestamp()::int4 AS result;
 
' LANGUAGE SQL;

</pre> <small><font face="Arial">The documentation indicates that the arguments are optional, and even shows an example
ofa function with no arguments. How can I create this function?</font></small><br /><div class="moz-signature">-- <br
/><p><fontcolor="#0000ff" face="Comic Sans MS">Michael Eshom<br /> Christian Oldies Fan<br /> Cincinnati,
Ohio</font></div>

Hello

current_timestamp is some more than less constant :)

try, please
postgres=# select extract(epoch from current_timestamp);   date_part
------------------1213030028.17068
(1 row)
or
postgres=# select extract(epoch from current_timestamp)::int;date_part
------------1213030113
(1 row)

Pavel


2008/6/9 Michael Eshom <oldiesmann@oldiesmann.us>:
> I am a project manager for a popular forum system. We are adding support for
> PostgreSQL in the next version (which is currently in beta), and have added
> several PostgreSQL functions to emulate MySQL functions of the same name.
>
> 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'.
>
> This is the SQL I'm using:
>
> CREATE FUNCTION unix_timestamp() RETURNS integer AS '
>     SELECT current_timestamp()::int4 AS result;
> ' LANGUAGE SQL;
>
> The documentation indicates that the arguments are optional, and even shows
> an example of a function with no arguments. How can I create this function?
> --
>
> Michael Eshom
> Christian Oldies Fan
> Cincinnati, Ohio

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

IIRC, current_timestamp doesn't require parens.  You could try something
like this:

select extract(epoch from current_timestamp)::int4 as result;

-Mark
On Mon, 2008-06-09 at 12:05 -0400, Michael Eshom wrote:
> I am a project manager for a popular forum system. We are adding
> support for PostgreSQL in the next version (which is currently in
> beta), and have added several PostgreSQL functions to emulate MySQL
> functions of the same name.
> 
> 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'.
> 
> This is the SQL I'm using:
> 
> CREATE FUNCTION unix_timestamp() RETURNS integer AS '
>     SELECT current_timestamp()::int4 AS result;
> ' LANGUAGE SQL;
> 
> The documentation indicates that the arguments are optional, and even
> shows an example of a function with no arguments. How can I create
> this function?
> -- 
> 
> 
> Michael Eshom
> Christian Oldies Fan
> Cincinnati, Ohio
> 
>