Thread: my pgsql error?

my pgsql error?

From

Christopher Sawtell

Date:

13 March 2001, 09:56:46

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 'declaret 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 SawtellCELL PHONE 021 257 4451ICQ UIN    45863470EMAIL      csawtell @ xtra . co . nzCNOTES
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 
<<--

Re: my pgsql error?

From

"Richard Huxton"

Date:

13 March 2001, 11:04:25

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

Re: my pgsql error?

From

Christof Glaser

Date:

13 March 2001, 11:19:09

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

Re: my pgsql error?

From

Christopher Sawtell

Date:

13 March 2001, 19:43:44

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 SawtellCELL PHONE 021 257 4451ICQ UIN    45863470EMAIL      csawtell @ xtra . co . nzCNOTES
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 
<<--

Re: my pgsql error?

From

"Richard Huxton"

Date:

14 March 2001, 08:26:17

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
supposedto be compatible with Oracle so it might be something Oracle does.
 

- Richard Huxton

Re: my pgsql error?

From

Tom Lane

Date:

14 March 2001, 12:50:08

> 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

Re: my pgsql error?

From

Roland Roberts

Date:

14 March 2001, 15:53:25

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