Help me in solving this Postgresql query

Question

The Postgresql query below should return the following date type ti varchar() type but its giving error. I've even tried it with convert(using return also), but still it's giving error.

CREATE FUNCTION TimePart( fDate out date )
--RETURNS date
AS $$
BEGIN

 cast(fDate AS char(10));

END;
$$ language 'sql'

Answer 1

1. sql functions do not use begin -> end - it's only in plpgsql
2. (fDate out date) ? what kind of syntax is it?
3. why is returns date commented out?
4. why there is no "select" in cast in body of function?
5. there is no point in this function - you can simply use select some_date_column::varchar on your own in sql query
6. do not use char() datatype - it's not faster than varchar, and can lead to subtle (or not so subtle errors).
7. sql language doesn't accept named parameters (fDate)
8. datepart is already name of built in function - and overriding it it definitely bad idea.

Assuming you want function which converts date parameter to some varchar value (which you don't need any function to do - what i pointed in point #5 above) you could use:

create function date_to_varchar(date) returns varchar as $$
select $1::varchar;
$$ language sql;

Answer 2

Try this:

CREATE FUNCTION TimePart( fDate out date )
--RETURNS date
AS $$
BEGIN

    fDate := cast(fDate AS varchar(10));

END;
$$ language 'plpgsql'

If you're going to use variables, you need to use plpgsql as the language. Also, use a varchar. That way you won't have to spend cycles trying to trim it down.

Answer 3

To convert the input date to a varchar, try this:

CREATE FUNCTION TimePart(fDate date)
RETURNS varchar AS
$$
BEGIN
    return fDate::varchar;
END;
$$ LANGUAGE 'plpgsql';