| 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: | Whole Thread | Raw Message | 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
| 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 |