From: Richard Huxton <dev@archonet.com>
To: Christopher Sawtell <csawtell@xtra.co.nz>
To: pgsql-sql@postgresql.org
Subject: Re: my pgsql error?
Date: Tue, 13 Mar 2001 12:38:22 -0000
Message-ID: <004001c0abba$8e8dd360$1001a8c0@archonet.com> (raw)
References: <01031400530600.00996@berty>
From: "Christopher Sawtell" <csawtell@xtra.co.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, csawtell@xtra.co.nz
Subject: Re: my pgsql error?
In-Reply-To: <004001c0abba$8e8dd360$1001a8c0@archonet.com>
References: <01031400530600.00996@berty> <004001c0abba$8e8dd360$1001a8c0@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox