Re: type cast/validation functions
От | Michael Fuhr |
---|---|
Тема | Re: type cast/validation functions |
Дата | |
Msg-id | 20041229013141.GA75402@winnie.fuhr.org обсуждение исходный текст |
Ответ на | Re: type cast/validation functions ("Robert Wimmer" <seppwimmer@hotmail.com>) |
Список | pgsql-interfaces |
On Wed, Dec 29, 2004 at 12:12:53AM +0000, Robert Wimmer wrote: > > > >Would 8.0's subtransactions, in particular PL/pgSQL's new error > >trapping construct, solve your problem? > > that is what i have been looking for - as i posted my problem the first > time. but i was told there is absolutly no way to catch any errors in > plpgsql - so i wrote my parser extensions. Prior to 8.0 that was true. There might also have been some misunderstanding about what you were looking for. > besides you only can produce very modest error codes this way. What would you like to be able to do? > another problem is > > - i use postgresql 7.2 (debian stable (how can i update ?)) See the "Installation Instructions" chapter of the PostgreSQL documentation. > - couldnt find any documentation about this new features in postgresql 8.0 The 8.0 Release Notes don't mention PL/pgSQL's error trapping but they do mention Savepoints. The PL/pgSQL chapter in the documentation describes error trapping in the "Control Structures" section. > - another drawback using postgres type cast functions (or the most standard > type cast functions) is, that they try to be some sort of 'intelligent', > but i want strict validation . so a date like '04-30-02' wil be casted to > 30th Jan 2004. Eh? Are you sure you wrote that correctly? Could you copy and paste the exact SQL statement you executed and the exact output? With my system's settings, '04-30-02' becomes '2002-04-30', or 30 Apr 2002 (but see the discussion of DateStyle below). > why 2004 ? and not 1904 my grandfather was born this year. Why 1904 and not 2004, the year lots of other people were born? Any time you deal with two-digit years you're going to run into this ambiguity. You're also going to have trouble with date format conventions that vary from country to country -- some write DD-MM-YY, while others write MM-DD-YY. See PostgreSQL's DateStyle configuration variable (introduced in 7.3) for a way to tell PostgreSQL which style it should prefer. > and '04-02-03' will be casted to 3rd Feb 2004 so the second date field is > the month, in the first example the 3rd field is the month field. this is > in conflict to ISO 8061 and pseudo intelligent. this is that sort of > incosistent behavior i dont like and at least is userUNfriendly. I think you mean ISO 8601. PostgreSQL 7.3 introduced the DateStyle configuration variable so you can tell PostgreSQL how to interpret dates: SET DateStyle TO ISO, DMY; SELECT '01-02-03'::DATE; date ------------2003-02-01 (1 row) SET DateStyle TO ISO, MDY; SELECT '01-02-03'::DATE; date ------------2003-01-02 (1 row) SET DateStyle TO ISO, YMD; SELECT '01-02-03'::DATE; date ------------2001-02-03 (1 row) You could also use the to_date() function: SELECT to_date('01-02-03', 'MM-DD-YY'); to_date ------------2003-01-02 (1 row) > if you can give me a hint where i find more about postgre 8.0 i really > would be pleased See the Release Notes appendix in the PostgreSQL 8.0 documentation. You can get it via FTP or BitTorrent by clicking "Downloads" on the PostgreSQL web site (http://www.postgresql.org/). You can also view the 8.0 documentation online by clicking the "Developers" link. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
В списке pgsql-interfaces по дате отправления:
Следующее
От: Kretschmer AndreasДата:
Сообщение: Re: [despammed] Re: type cast/validation functions