Re: Unable to create function which takes no arguments - Mailing list pgsql-sql

From hubert depesz lubaczewski
Subject Re: Unable to create function which takes no arguments
Date
Msg-id 20080609164921.GA26375@depesz.com
Whole thread Raw
In response to Unable to create function which takes no arguments  (Michael Eshom <oldiesmann@oldiesmann.us>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: "Pavel Stehule"
Date:
Subject: Re: Unable to create function which takes no arguments
Next
From: Mark Roberts
Date:
Subject: Re: Unable to create function which takes no arguments