fast insert-if-key-not-already-there

From: Patrick Scharrenberg <pittipatti(at)web(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: fast insert-if-key-not-already-there
Date: 2008-08-04 15:51:27
Message-ID: 4897257F.3020309@web.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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 $$
DECLARE
v_ip_addr INET;
v_ip_addr_id INTEGER := 0 ;
v_ip_addr_ids INTEGER[];
BEGIN
FOR 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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas Kellerer 2008-08-04 15:55:32 Re: fast insert-if-key-not-already-there
Previous Message Terry Lee Tucker 2008-08-04 15:45:40 Re: Case Insensitive searches