Re: insert

[alan <alan.miller3@xxxxxxxxx>]

I think I figured it out myself.
If anyone sees issues with this (simple) approach, please let me know.

I changed my table definitions to this:

CREATE SEQUENCE public.product_id_seq
CREATE TABLE products (
    product_id INTEGER DEFAULT nextval('product_id_seq'::regclass) NOT
NULL,
    name VARCHAR(60) NOT NULL,
    category SMALLINT  NOT NULL,
    CONSTRAINT product_id PRIMARY KEY (product_id)
);
CREATE SEQUENCE public.category_id_seq
CREATE TABLE category (
   category_id INTEGER DEFAULT nextval('category_id_seq'::regclass)
NOT NULL,
   name VARCHAR(20) NOT NULL,
   CONSTRAINT category_id PRIMARY KEY (category_id)
);
ALTER TABLE products ADD CONSTRAINT category_products_fk
    FOREIGN KEY (category)
    REFERENCES category (category_id)
    ON DELETE NO ACTION ON UPDATE CASCADE
;

Then created this function:

CREATE OR REPLACE FUNCTION getid(_table text,_pk text,_name text)
RETURNS integer AS $$
DECLARE _id integer;
BEGIN
  EXECUTE 'SELECT '
    || _pk
    || ' FROM '
    || _table::regclass
    || ' WHERE name'
    || ' = '
    || quote_literal(_name)
   INTO _id;

  IF _id > 0 THEN
    return _id;
  ELSE
    EXECUTE 'INSERT INTO '
     || _table
     || ' VALUES (DEFAULT,' || quote_literal(_name) || ')'
     || ' RETURNING ' || _pk
    INTO _id;
    return _id;
  END IF;
END;
$$
 LANGUAGE 'plpgsql' VOLATILE;

Now I can just insert into the products table via:

INSERT INTO products VALUES(DEFAULT,'Postgresql for
Dummies',getid('category','category_id','books'));

For example:

testdb=# select * from products;
 product_id | name | category
------------+------+----------
(0 rows)

iims_test=# select * from category;
 category_id | name
-------------+------
(0 rows)

testdb=# insert into products values(DEFAULT,'Postgresql for
Dummies',getid('category','category_id','books'));
INSERT 0 1

testdb=# select * from
category;
 category_id | name
-------------+-------
           1 | books

testdb=# select * from products;
 product_id |          name          | category
------------+------------------------+----------
          1 | Postgresql for Dummies |        1

Updating the category_id in category table are also cascaded to the
product table.

testdb=# UPDATE category SET category_id = 2 WHERE category_id = 1;
UPDATE 1

testdb=# SELECT * FROM products;
 product_id |          name          | category
------------+------------------------+----------
          1 | Postgresql for Dummies |        2


Alan

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance