Обсуждение: my pgsql error?
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
<<--
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
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
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 <<--
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
> 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
>>>>> "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