Horton

Thread

  1. my pgsql error?

    Christopher Sawtell <csawtell@xtra.co.nz> — 2001-03-13T11:53:05Z

    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 
<<--

  2. Re: my pgsql error?

    Richard Huxton <dev@archonet.com> — 2001-03-13T12:38:22Z

    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

  3. Re: my pgsql error?

    Christof Glaser <gcg@gl.aser.de> — 2001-03-13T13:19:21Z

    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

  4. Re: my pgsql error?

    Christopher Sawtell <csawtell@xtra.co.nz> — 2001-03-13T20:06:54Z

    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 
<<--

  5. Re: my pgsql error?

    Richard Huxton <dev@archonet.com> — 2001-03-14T09:54:33Z

    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

  6. Re: my pgsql error?

    Tom Lane <tgl@sss.pgh.pa.us> — 2001-03-14T14:49:51Z

    > 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

  7. Re: my pgsql error?

    Roland Roberts <roland@astrofoto.org> — 2001-03-14T17:53:15Z

    >>>>> "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