PostgreSQL insert that depends on data in another table, best practice?

Question

I've recently started using PostgreSQL and I'm trying to do things "the right way" as I've understood it. Meaning putting as much work in the database server instead of on the client as possible.

So I've created a function with PL/pgSQL that will add data to a table. Since I've a primary key constraint set on that table, and the referenced key may not exist at the time I'm trying to add the new data I've added an exception catching that will create the key and then try to insert the new row again.

This is working satisfactory for me, but I'm curious whether I'm handling this "the correct way". I've been trying to find some kind of guide of designing these user defined functions but havn't really found anything I found helpful.

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;

The database in question is going to be used to make reports and will be importing the complete item register every day with price updates and new items, but I won't know which items are new and which ones are old.

Answer 1

You're actually doing alright. I would recommend providing a boolean return value as well for your function for additional peace of mind while working with your stored procedure, and referencing your arguments by name unless you're planning on targetting older versions of PostgreSQL (in which case you'll need to add a DECLARE section).

I strongly recommend the book PostgreSQL (Developer's Library) by Korry Douglas which has a lot of material about writing stored procedures in Pl/PgSQL. I also recommending following the Planet PostgreSQL portal which aggregates blog posts from prominent members of the PostgreSQL community, as they very often discuss best-practices and clever tricks for solving complex problems using Postgres. Good luck!

Answer 2

No!!! WRONG WAY Sorry, I've been using postgresql for years and this is a bad idea. The right way is (1) to create a temp table, and (2) update where there is a violation (3) insert where there isn't a violation. I'll show you a snippet using pg 8.4:

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

Then you want to, insert all of your stuff into temp_table and run these two commands.

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 )
);

You can do it in a transaction if you care, and you have enough mem. This method scales because internally it doesn't create a massive amount of checkpoints. It is also massively faster. Your current way was published as a pseudo-merge routine on Varlena in 2006, it bit me, and it has bitten lots of people. I don't see a need for it, so I suggest you avoid it.

Answer 3

I think it's better to write code that should run without throwing exceptions in the normal case. Since you're in pl/pgSQL anyways, why not write it as:

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; 

According to the pl/pgSQL docs there's more overhead associated with a block that has an exception handler as opposed to one with no handler, so this might save some trivial amount of overhead.

Share and enjoy.