pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
my pgsql error?
7+ messages / 5 participants
[nested] [flat]

* my pgsql error?
@ 2001-03-13 11:53 Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-13 12:38 ` Re: my pgsql error? Richard Huxton <dev@archonet.com>
  2001-03-13 13:19 ` Re: my pgsql error? Christof Glaser <gcg@gl.aser.de>
  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 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   ` Re: my pgsql error? 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 11:53 my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-13 12:38 ` Re: my pgsql error? Richard Huxton <dev@archonet.com>
@ 2001-03-13 20:06   ` Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-14 09:54     ` Re: my pgsql error? 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-13 11:53 my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-13 12:38 ` Re: my pgsql error? Richard Huxton <dev@archonet.com>
  2001-03-13 20:06   ` Re: my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
@ 2001-03-14 09:54     ` Richard Huxton <dev@archonet.com>
  2001-03-14 14:49       ` Re: my pgsql error? Tom Lane <tgl@sss.pgh.pa.us>
  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-13 11:53 my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-13 12:38 ` Re: my pgsql error? Richard Huxton <dev@archonet.com>
  2001-03-13 20:06   ` Re: my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-14 09:54     ` Re: my pgsql error? Richard Huxton <dev@archonet.com>
@ 2001-03-14 14:49       ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-14 17:53         ` Re: my pgsql error? Roland Roberts <roland@astrofoto.org>
  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-13 11:53 my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-13 12:38 ` Re: my pgsql error? Richard Huxton <dev@archonet.com>
  2001-03-13 20:06   ` Re: my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
  2001-03-14 09:54     ` Re: my pgsql error? Richard Huxton <dev@archonet.com>
  2001-03-14 14:49       ` Re: my pgsql error? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-14 17:53         ` Roland Roberts <roland@astrofoto.org>
  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

* Re: my pgsql error?
  2001-03-13 11:53 my pgsql error? Christopher Sawtell <csawtell@xtra.co.nz>
@ 2001-03-13 13:19 ` Christof Glaser <gcg@gl.aser.de>
  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


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