Postgres (8.0.2., Redshift) SQL function definition, error "The cursor is not located inside a statement"

Question

I am a super noob in postgresql. But I need to define a func that would map: int --> datetime After reading documentation I've come up with this:

 CREATE FUNCTION fut(num integer) RETURNS datetime
 -- convert a UNIX time integer into the datetime timestamp
    AS $$ select timestamp 'epoch'+interval '1 second'*num; $$
    LANGUAGE plpgsql;

select fut(500);

But it returns

The cursor is not located inside a statement!

Could you please point me to what I am doing wrong here?

Answer 1

As far as I knew Redshift doesn't even permit user-defined functions. Yeah: http://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html : User-defined functions and stored procedures. So I think you're plain out of luck.

A search for Redshift epoch to timestamp or redshift to_timestamp finds that lots of other people have looked into this already:

• http://yiyujia.blogspot.com.au/2014/04/redshift-convert-integer-to-timestamp.html
• http://www.valkrysa.com/tech/2015/1/30/amazon-redshift-converting-unix-epoch-time-into-timestamps

etc.

The most sensible answers are those that rely on:

TIMESTAMP 'epoch' + myunixtime * INTERVAL '1 Second '

which is what you appear to be already doing. This the best you are going to get, because Redshift does not support user-defined functions.

Answer 2

As far as I understand from your function, you want to convert unix time (i.e., the number of seconds since 00:00:00 UTC, 1 January 1970) saved as a value of integer type, to a value of valid postgresql date/time type.

If this is so, you do not have to create a new function for such a conversion, just use the predefined postgresql function to_timestamp, for example:

# select to_timestamp(500);
to_timestamp      
------------------------
 1970-01-01 03:08:20+03
(1 row)