Re: my pgsql error?

From: "Christopher Sawtell" <csawtell(at)xtra(dot)co(dot)nz>

> Please could a kind soul help me with this.
> I can't find _any_ - however cryptic - docs about plpgsql.

You need to look in the User's guide Chapter 9 (in the pre-release docs
anyway)

> create function nice_date(date) returns varchar as '
> declare
> t alias for $1;
> d text;
> m text;
> y text;
> begin
> day := rtrim(to_char(\'t\'::timestamp, \'Day\'));
> month := rtrim(to_char(\'t\'::timestamp, \'DD Month\'));
> year := rtrim(to_char(\'t\'::timestamp, \'YYYY\' ));
> nd := d || m || y;
> end;
> return nd;
> end;' language 'plpgsql';

Two "end;" lines - remove the first to fix the error you are getting.

Also - you define d,m,y and use day,month,year
You don't define nd
The to_char lines you are using will try and convert the literal string 't'
to a timestamp.
You will want some spaces in the nd value.

So, your script will become:

create function nice_date(date) returns varchar as '
declare
t alias for $1;
d text;
m text;
y text;
nd text;
begin
d := rtrim(to_char(t::timestamp, \'Day\'));
m := rtrim(to_char(t::timestamp, \'DD Month\'));
y := rtrim(to_char(t::timestamp, \'YYYY\' ));
nd := d || \' \' || m || \' \' || y;
return nd;
end;' language 'plpgsql';

Note to readers: this is not a general service, I'm in a good mood ;-)

For a moment I thought you could do to_char(now(),'Day DD Month YYYY' but
you're quite right you need to rtrim() the various pieces.

- Richard Huxton