日期无效时引发错误 [英] Raise error when date is not valid

问题描述

我要执行的操作是在日期超出支持范围的情况下引发超出范围的错误，例如类型转换。

What I'm trying to do is to raise out of range error in case of dates outside of the supported range like what typecasting does.

我正在使用CentOS上的PostgreSQL-9.1.6。问题在下面...

I'm using PostgreSQL-9.1.6 on CentOS. The issue is below...

postgres=# select to_date('20130229','yyyymmdd');
  to_date   
------------
 2013-03-01
(1 row)

但是我想看到的输出是：

But the output I want to see is:

postgres=# select '20130229'::date;
ERROR:  date/time field value out of range: "20130229"

网上冲浪我找到了信息页。所以我确实将 IS_VALID_JULIAN 添加到了 to_date 的函数体中，并添加了标有 +的四行。 到 formatting.c ：

Surfing the web I found an informative page. So I did adding IS_VALID_JULIAN to the function body of to_date, adding the four lines marked + below to formatting.c:

Datum
to_date(PG_FUNCTION_ARGS)
{
    text       *date_txt = PG_GETARG_TEXT_P(0);
    text       *fmt = PG_GETARG_TEXT_P(1);
    DateADT         result;
    struct pg_tm tm;
    fsec_t          fsec;

    do_to_timestamp(date_txt, fmt, &tm, &fsec);

+       if (!IS_VALID_JULIAN(tm.tm_year, tm.tm_mon, tm.tm_mday))
+       ereport(ERROR,
+               (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+                errmsg("date out of range: \"%s\"",text_to_cstring(date_txt))));

   result = date2j(tm.tm_year, tm.tm_mon, tm.tm_mday) - POSTGRES_EPOCH_JDATE;

   PG_RETURN_DATEADT(result);
}

然后我重建了PostgreSQL：

Then I rebuilt PostgreSQL:

pg_ctl -m fast stop                       # 1. stopping pgsql
vi src/backend/utils/adt/formatting.c     # 2. using the version above
rm -rf /usr/local/pgsql/*                 # 3. getting rid of all bin files
./configure --prefix=/usr/local/pgsql 
  --enable-nls --with-perl --with-libxml
  --with-pam --with-openssl
make && make install                      # 4. rebuilding source    
pg_ctl start                              # 5. starting the engine

我的垃圾箱目录信息如下。

My bin directory info is below.

[/home/postgres]echo $PATH
/usr/lib64/qt-3.3/bin:
/usr/local/bin:
/bin:
/usr/bin:
/usr/local/sbin:
/usr/sbin:
/sbin:
/home/postgres/bin:
/usr/bin:
/usr/local/pgsql/bin:
/usr/local/pgpool/bin:
/usr/local/pgtop/bin/pg_top:

[/home/postgres]which pg_ctl
/usr/local/pgsql/bin/pg_ctl

[/home/postgres]which postgres
/usr/local/pgsql/bin/postgres

[/usr/local/bin]which psql
/usr/local/pgsql/bin/psql

但是在检查 to_date ，结果保持不变。

But upon checking to_date again, the result remained the same.

postgres=# select to_date('20130229','yyyymmdd');
  to_date   
------------
 2013-03-01
(1 row)

有什么我想念的吗？

推荐答案

您可以编写自己的to_date（）函数，但必须使用其架构限定名称来调用它。 （我使用了 public模式，但是没有什么特别的。）

You can write your own to_date() function, but you have to call it with its schema-qualified name. (I used the schema "public", but there's nothing special about that.)

create or replace function public.to_date(any_date text, format_string text)
returns date as
$$
select to_date((any_date::date)::text, format_string);
$$
language sql

使用裸函数名称执行本机to_date

Using the bare function name executes the native to_date() function.

select to_date('20130229', 'yyyymmdd');
2013-03-01

使用架构限定名称执行用户定义的功能

Using the schema-qualified name executes the user-defined function.

select public.to_date('20130229', 'yyyymmdd');
ERROR: date/time field value out of range: "20130229"
SQL state: 22008

我知道那不是您要的东西。但是。 。 。

I know that's not quite what you're looking for. But . . .

• 比从源代码重建PostgreSQL更简单。


• 修复现有的SQL和PLPGSQL源代码使用流式编辑器进行简单的搜索和替换。只要您真的想要每次都将本机to_date（）公开使用，就可以肯定会出错。


• 本机的to_date（）函数仍将按设计工作。扩展和其他代码可能依赖于其某些特殊行为。在更改本机函数的行为之前，请三思而后行。

• It's simpler than rebuilding PostgreSQL from source.
• Fixing up your existing SQL and PLPGSQL source code is a simple search-and-replace with a streaming editor. I'm pretty sure that can't go wrong, as long as you really want every use of the native to_date() to be public.to_date().
• The native to_date() function will still work as designed. Extensions and other code might rely on its somewhat peculiar behavior. Think hard and long before you change the behavior of native functions.

不过，新的SQL和PLPGSQL需要进行审查。我不希望开发人员每次都记得写public.to_date（）。如果使用版本控制，则可以编写一个预提交钩子，以确保仅使用public.to_date（）。

New SQL and PLPGSQL would need to be reviewed, though. I wouldn't expect developers to remember to write public.to_date() every time. If you use version control, you might be able to write a precommit hook to make sure only public.to_date() is used.

本机to_date（）函数具有行为我没有记录在案。您不仅可以在2月29日之前调用它，还可以在2月345日或9999年2月调用它。

The native to_date() function has behavior I don't see documented. Not only can you call it with February 29, you can call it with February 345, or February 9999.

select to_date('201302345', 'yyyymmdd');
2014-01-11

select to_date('2013029999', 'yyyymmdd');
2040-06-17

这篇关于日期无效时引发错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！