Informix -使用weekday函数创建表

Question

我正在尝试在Informix (11.70)上创建一个表。我想在CREATE子句的日期时间值中添加函数WEEKDAY，以便在插入数据时自动返回一个整数。

例如。

     CREATE TABLE orders (

     order_num serial
     order_date datetime year to second
     order_weekday datetime year to second
     )

我已经尝试了下面的方法，但是我得到了语法错误。

CREATE TABLE orders (

     order_num serial
     order_date datetime year to second
     order_weekday WEEKDAY(datetime) year to second
     )

还有这个

CREATE TABLE orders (

     order_num serial
     order_date datetime year to second
     WEEKDAY(order_weekday) datetime year to second
     )

有什么方法可以做到这一点吗？

Answer 1

最好的方法可能是对每一行使用triggers，它在插入和更新时根据Order-Date列中的值分配给工作日列。

正如RET在他的answer中指出的那样，最好不要存储派生数据，特别是不要存储像工作日那样容易派生的数据。

但是，假设您需要这样做，那么您的触发器将如下所示：

BEGIN WORK;
CREATE TABLE orders
(
    order_num SERIAL NOT NULL,
    order_date DATETIME YEAR TO SECOND NOT NULL,
    order_weekday INTEGER CHECK (order_weekday BETWEEN 0 AND 6) NOT NULL
);
INSERT INTO orders VALUES(0, CURRENT YEAR TO SECOND, WEEKDAY(MOD(WEEKDAY(TODAY) + 3, 7)));
SELECT *, WEEKDAY(order_date) AS calc_weekday FROM orders;

order_num order_date order_weekday calc_weekday序列日期时间年份到第二个整数SMALLINT 1 2016-02-04 23:21:36 0 4

CREATE TRIGGER i_orders INSERT ON orders
    REFERENCING NEW AS NEW
    FOR EACH ROW
    (
        UPDATE orders
           SET order_weekday = WEEKDAY(NEW.order_date)
         WHERE order_num = NEW.order_num
    );
CREATE TRIGGER u_orders UPDATE OF order_date ON orders
    REFERENCING NEW AS NEW
    FOR EACH ROW
    (
        UPDATE orders
           SET order_weekday = WEEKDAY(NEW.order_date)
         WHERE order_num = NEW.order_num
    );
INSERT INTO orders(order_num, order_date) VALUES(0, CURRENT YEAR TO SECOND);
SELECT *, WEEKDAY(order_date) AS calc_weekday FROM orders;

order_num order_date order_weekday calc_weekday序列日期时间年份到第二个整数SMALLINT 1 2016-02-04 23:21:36 0 4 2 2016-02-04 23:21:36 4 4

UPDATE orders
   SET order_date = order_date - 10 UNITS DAY
 WHERE order_weekday != WEEKDAY(order_date);
SELECT *, WEEKDAY(order_date) AS calc_weekday FROM orders;

order_num order_date order_weekday calc_weekday序列日期时间年份到第二个整数SMALLINT 1 2016-01-25 23:21:36 1 2 2016-02-04 23:21:36 4 4

ROLLBACK WORK;

Answer 2

你想要达到什么目的？RDBMS规范化的一个基本原则是，您不应该存储可以从另一个现有字段派生的值。您可以在任何需要的时候计算WEEKDAY(order_date)，实际上是免费的。

然而，如果你真的想这样做，你需要做的就是编写一个插入触发器，就像我在写这篇文章时@JonathanLeffer建议的那样。

你可能还想弄清楚“返回一个整数”是什么意思。数据库在一个成功的INSERT上返回一些东西，但是您不能任意地覆盖它。如果您真的想这样做，则需要编写一个过程来处理将记录插入到orders表并返回所需的值。