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

Question

I recently started using PostgreSQL, and I'm trying to make something “right,” as I understand it. This means that as much work as possible on the database server, and not on the client, is possible.

So, I created a function with PL / pgSQL that will add data to the table. Since I have a primary key constraint set in this table, and the reference key may not exist while I'm trying to add new data, I added an exception that will create the key and then try to insert a new row again.

This works satisfactorily for me, but I am curious if I am processing this "right path". I tried to find some kind of design guide for these custom functions, but did not find anything useful.

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;

This database will be used for reporting and will import a complete goods registrar every day with price updates and new items, but I will not know which items are new and which are old.

Answer 1

!!! WRONG WAY , postgresql , . : (1) (2) , (3), , . , pg 8.4:

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

temp_table .

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

, , . , . . - Varlena 2006 , , . , .

Answer 2

. , PostgreSQL ( DECLARE).

PostgreSQL ( ) Korry Douglas, Pl/PgSQL, Planet PostgreSQL, PostgreSQL, Postgres. !

Answer 3

, , . 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; 

pl/pgSQL , , , , .

.