Received: from candle.pha.pa.us (nav-43.dsl.navpoint.com [162.33.245.46]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eA23dfl15592 for ; Wed, 1 Nov 2000 22:39:41 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id WAA11211; Wed, 1 Nov 2000 22:38:54 -0500 (EST) From: Bruce Momjian Message-Id: <200011020338.WAA11211@candle.pha.pa.us> Subject: Re: INSERT [IGNORE] INTO TABLE In-Reply-To: <3A00594A.AA3A3120@mondo-live.com> "from Najm Hashmi at Nov 1, 2000 12:56:26 pm" To: Najm Hashmi Date: Wed, 1 Nov 2000 22:38:54 -0500 (EST) CC: Edmar Wiggers , "pgsql-sql@postgresql.org" X-Mailer: ELM [version 2.4ME+ PL77 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200011/10 X-Sequence-Number: 41 The proper fix for this is subtransactions, which we will have someday. You do the INSERT in a subtransaction. > Edmar Wiggers wrote: > > > > If I try to insert some row which in the table already exist the > > > postgre don't insert it and tell some error. Everything ok. > > > I'll insert only if there is not the same (same key) record, > > > else don't insert and don't tell me errors about it. > > > In MySQL is a switch [IGNORE]. > > > > Not a good feature for me. What do you do if the record already exists? > > Update it? > > > > Check existence and then insert or update. If you want, I guess you could > > wrap that inside a stored procedure. > > Hi, here is an example of using function using pl/pgsql for inserting and > checking whether an instance exists or not..... > CREATE FUNCTION add_new_user(text,text) RETURNS bool AS' DECLARE > oldUser RECORD; > USR ALIAS FOR $1; > PWORD ALIAS FOR $2; > > BEGIN > SELECT INTO oldUser * > FROM users > where username=USR AND password= PWORD; > IF FOUND > THEN > RETURN ''f''; > ELSE > INSERT INTO USERS(username,password) > values(USR,PWORD); > RETURN ''t''; > END IF; > > END;' > LANGUAGE 'plpgsql'; > > Regards. > Najm > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026