Hi!
I have to do much inserts into a database where the key most often is
already there.
My current approach is to query for the key (ip-address), and if the
result is null I do the insert.
For every IP-Address I need the ip_addr_id from the same table.
Something like this:
CREATE TABLE ip_addresses ("ip_addr_id" serial NOT NULL,"ip_addr" inet UNIQUE NOT NULL
PRIMARY KEY(ip_addr);
);
CREATE OR REPLACE FUNCTION update_Addresses(v_ip_addresses inet[]
) RETURNS void AS $$
DECLAREv_ip_addr INET;v_ip_addr_id INTEGER := 0 ;v_ip_addr_ids INTEGER[];
BEGINFOR i IN 1..( array_upper( v_ip_addresses, 1 ) )::integer LOOP v_ip_addr = v_ip_addresses[i];
-- check if ip_addr exists and append if not SELECT ip_addr_id FROM ip_addresses WHERE ip_addr=v_ip_addr INTO
v_ip_id; IF v_ip_id IS NULL THEN INSERT INTO ip_addresses ( ip_addr ) VALUES( v_ip_addr ) RETURNING
ip_addr_id INTO v_ip_id ; END IF;
v_ip_addr_ids = array_append(v_ip_addr_ids, v_ip_addr_id);
END LOOP;
END;
$$ LANGUAGE 'plpgsql' STRICT;
Now I'm wondering if there is a better solution, since I'm doing ~20
inserts at once and every time I'm doing single lookup's for the IDs.
regards
patrick
