代码之家 › 专栏 › 技术社区 › gaqzi

依赖于另一个表中数据的PostgreSQL插入,最佳做法?

user-defined-functions function postgresql

gaqzi · 技术社区 · 16 年前

因此,我使用PL/pgSQL创建了一个函数,该函数将向表中添加数据。由于在该表上设置了主键约束,并且在尝试添加新数据时引用的键可能不存在,因此我添加了一个异常捕获,该异常捕获将创建键,然后再次尝试插入新行。

这对我来说是令人满意的,但我很好奇我是否以“正确的方式”处理这件事。我一直试图找到一些设计这些用户定义函数的指南,但没有发现任何有用的东西。

CREATE OR REPLACE FUNCTION add_product_price_promo_xml(v_product_code varchar, v_description varchar, v_product_group varchar,
                                                       v_mixmatch_id integer, v_price_at date, v_cost_price numeric, v_sales_price numeric,
                                                       v_tax_rate integer) RETURNS void AS $$
BEGIN
   INSERT INTO product_prices (product_code  , mixmatch_id  , price_at  , cost_price  , sales_price  , tax_rate) VALUES
                              (v_product_code, v_mixmatch_id, v_price_at, v_cost_price, v_sales_price, v_tax_rate);
EXCEPTION WHEN foreign_key_violation THEN
   INSERT INTO products (code, description, product_group) VALUES (v_product_code, v_description, v_product_group);
   PERFORM add_product_price_promo_xml($1, $2, $3, $4, $5, $6, $7, $8);
END;
$$ LANGUAGE plpgsql;

有问题的数据库将用于制作报告,并将每天导入完整的商品登记簿,其中包含价格更新和新商品,但我不知道哪些商品是新的,哪些是旧的。

3 回复 | 直到 16 年前

Evan Carroll 16 年前

不 走错路 对不起,我已经使用postgresql很多年了,这是个坏主意。正确的方法是(1)创建临时表,(2)在有冲突的地方更新(3)在没有冲突的地方插入。我将使用第8.4页向您展示一个片段:

CREATE TEMP TABLE temp_table (
  LIKE table INCLUDING INDEXES INCLUDING CONSTRAINTS
);

然后,您需要将所有内容插入temp_表并运行这两个命令。

UPDATE table
SET a = t.a
FROM temp_table AS t
WHERE join-constraints;

INSERT INTO table
SELECT * FROM temp_table AS t
WHERE NOT EXISTS (
    SELECT * FROM table AS v
    WHERE ( join-constraints )
);

checkpoints . 也是 大量地 pseudo-merge routine on Varlena in 2006 ,它咬了我,还咬了很多人。我觉得没有必要,所以我建议你避免。

loginx 16 年前

你实际上做得很好。我建议为函数提供一个布尔返回值,以便在使用存储过程时更加安心,并按名称引用参数,除非您计划以较旧版本的PostgreSQL为目标(在这种情况下,您需要添加DECLARE部分)。

我强烈推荐这本书 PostgreSQL (Developer's Library) by Korry Douglas 其中有很多关于用Pl/PgSQL编写存储过程的资料。我还建议: Planet PostgreSQL 该门户网站汇集了PostgreSQL社区知名成员的博客文章,因为他们经常讨论使用Postgres解决复杂问题的最佳实践和巧妙技巧。祝你好运

Bob Jarvis - Ð¡Ð»Ð°Ð²Ð° Ð£ÐºÑÐ°ÑÐ½Ñ 16 年前

我认为最好编写在正常情况下不抛出异常的代码。既然您使用的是pl/pgSQL,为什么不将其编写为:

CREATE OR REPLACE FUNCTION add_product_price_promo_xml(v_product_code  varchar,
                                                       v_description   varchar,
                                                       v_product_group varchar, 
                                                       v_mixmatch_id integer,
                                                       v_price_at date,
                                                       v_cost_price numeric, 
                                                       v_sales_price numeric, 
                                                       v_tax_rate integer)
  RETURNS void AS $$ 
DECLARE
  n_count  numeric;
BEGIN
  SELECT COUNT(*)
    FROM products
    INTO n_count
    WHERE code = v_product_code;  -- or whatever the join criteria should be

  IF n_count = 0 THEN
    INSERT INTO products
      (code, description, product_group)
    VALUES
      (v_product_code, v_description, v_product_group);
  END IF;

  INSERT INTO product_prices
    (product_code, mixmatch_id, price_at,
     cost_price, sales_price, tax_rate)
  VALUES 
    (v_product_code, v_mixmatch_id, v_price_at,
     v_cost_price, v_sales_price, v_tax_rate); 
END; 
$$ LANGUAGE plpgsql;

分享和享受。