pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedmy pgsql error?
7+ messages / 5 participants
[nested] [flat]
* my pgsql error?
@ 2001-03-13 11:53 Christopher Sawtell <csawtell@xtra.co.nz>
0 siblings, 2 replies; 7+ messages in thread
From: Christopher Sawtell @ 2001-03-13 11:53 UTC (permalink / raw)
To: pgsql-sql
Please could a kind soul help me with this.
I can't find _any_ - however cryptic - docs about plpgsql.
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';
It seems to load ok, but on exection I get this message.
chris=# select nice_date(date('today'));
NOTICE: plpgsql: ERROR during compile of nice_date near line 12
ERROR: parse error at or near "return"
Please, what am I doing wrong?
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: my pgsql error?
@ 2001-03-13 12:38 Richard Huxton <dev@archonet.com>
parent: Christopher Sawtell <csawtell@xtra.co.nz>
1 sibling, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2001-03-13 12:38 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: my pgsql error?
@ 2001-03-13 13:19 Christof Glaser <gcg@gl.aser.de>
parent: Christopher Sawtell <csawtell@xtra.co.nz>
1 sibling, 0 replies; 7+ messages in thread
From: Christof Glaser @ 2001-03-13 13:19 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
On Tuesday, 13. March 2001 12:53, Christopher Sawtell wrote:
> Please could a kind soul help me with this.
> I can't find _any_ - however cryptic - docs about plpgsql.
Click here:
http://www.postgresql.org/devel-corner/docs/programmer/plpgsql.html
> create function nice_date(date) returns varchar as '
> declare
> t alias for $1;
> d text;
> m text;
> y text;
> begin
Well, use the declared variable names instead of day, month, year:
d := rtrim(to_char(\'t\'::timestamp, \'Day\'));
m := rtrim(to_char(\'t\'::timestamp, \'DD Month\'));
y := rtrim(to_char(\'t\'::timestamp, \'YYYY\' ));
Just remove the following three lines ...
> nd := d || m || y;
> end;
> return nd;
... and write instead:
return d || m || y;
> end;' language 'plpgsql';
>
> It seems to load ok, but on exection I get this message.
>
> chris=# select nice_date(date('today'));
> NOTICE: plpgsql: ERROR during compile of nice_date near line 12
> ERROR: parse error at or near "return"
>
> Please, what am I doing wrong?
There must be only one 'end;' at the, um, end of the function.
Hope that helps,
Christof
--
gl.aser . software engineering . internet service
http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg
Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: my pgsql error?
@ 2001-03-13 20:06 Christopher Sawtell <csawtell@xtra.co.nz>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Christopher Sawtell @ 2001-03-13 20:06 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; pgsql-sql
On Wed, 14 Mar 2001 01:38, Richard Huxton wrote:
> From: "Christopher Sawtell" <csawtell@xtra.co.nz>
>
> > Please could a kind soul help me with this.
[ ... ]
> Note to readers: this is not a general service, I'm in a good mood ;-)
In that case, thank you very much indeed, and may the blessings of
the Deities be on you.
> 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.
Personally I think the fact that you have to do all that rtrim() stuff is
very close to being a bug. What do you think?
--
Sincerely etc.,
NAME Christopher Sawtell
CELL PHONE 021 257 4451
ICQ UIN 45863470
EMAIL csawtell @ xtra . co . nz
CNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz
-->> Please refrain from using HTML or WORD attachments in e-mails to me
<<--
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: my pgsql error?
@ 2001-03-14 09:54 Richard Huxton <dev@archonet.com>
parent: Christopher Sawtell <csawtell@xtra.co.nz>
0 siblings, 1 reply; 7+ messages in thread
From: Richard Huxton @ 2001-03-14 09:54 UTC (permalink / raw)
To: Christopher Sawtell <csawtell@xtra.co.nz>; Richard Huxton <dev@archonet.com>; pgsql-sql
Christopher Sawtell <csawtell@xtra.co.nz> said:
> On Wed, 14 Mar 2001 01:38, Richard Huxton wrote:
> > From: "Christopher Sawtell" <csawtell@xtra.co.nz>
> >
> > > Please could a kind soul help me with this.
>
> [ ... ]
>
> > Note to readers: this is not a general service, I'm in a good mood ;-)
> In that case, thank you very much indeed, and may the blessings of
> the Deities be on you.
>
> > 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.
>
> Personally I think the fact that you have to do all that rtrim() stuff is
> very close to being a bug. What do you think?
>
It certainly comes under the headings of "unexpected" and "making more work for me" but I seem to remember it's supposed to be compatible with Oracle so it might be something Oracle does.
- Richard Huxton
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: my pgsql error?
@ 2001-03-14 14:49 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2001-03-14 14:49 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Christopher Sawtell <csawtell@xtra.co.nz>; pgsql-sql
> 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.
regression=# select to_char(now()-10,'Day DD Month YYYY');
to_char
-----------------------------
Sunday 04 March 2001
(1 row)
regression=# select to_char(now()-10,'fmDay fmDD fmMonth YYYY');
to_char
---------------------
Sunday 4 March 2001
(1 row)
The 'fm' prefix is not very well explained in the docs at
http://www.postgresql.org/devel-corner/docs/postgres/functions-formatting.html
If anyone wants to suggest better wording, I'll be glad to put it in.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: my pgsql error?
@ 2001-03-14 17:53 Roland Roberts <roland@astrofoto.org>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Roland Roberts @ 2001-03-14 17:53 UTC (permalink / raw)
To: pgsql-sql
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>> 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.
Tom> regression=# select to_char(now()-10,'Day DD Month YYYY');
Tom> to_char
Tom> -----------------------------
Tom> Sunday 04 March 2001
Tom> (1 row)
Tom> regression=# select to_char(now()-10,'fmDay fmDD fmMonth YYYY');
Tom> to_char
Tom> ---------------------
Tom> Sunday 4 March 2001
Tom> (1 row)
Tom> The 'fm' prefix is not very well explained in the docs at
It doesn't seem to be explained at all, just listed in the table. I'd
suggest adding the following (and perhaps include your example selects from
above) to the usage notes below table 4-11:
o to_char() does not remove trailing blanks from fields which are
blank padded, e.g., `Day' and `Month', unless the `FM' prefix is
used.
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
roland@rlenter.com 76-15 113th Street, Apt 3B
rbroberts@acm.org Forest Hills, NY 11375
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2001-03-14 17:53 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-13 11:53 my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
2001-03-13 12:38 ` Richard Huxton <dev@archonet.com>
2001-03-13 20:06 ` Christopher Sawtell <csawtell@xtra.co.nz>
2001-03-14 09:54 ` Richard Huxton <dev@archonet.com>
2001-03-14 14:49 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-14 17:53 ` Roland Roberts <roland@astrofoto.org>
2001-03-13 13:19 ` Christof Glaser <gcg@gl.aser.de>
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