Based on some of the stackoverflow answers I wrote the following
UPSERT statement, which I think must be stuck in an infinite loop. I added a statement in there to avoid an infinite loop (as it would be possible in this case for the insert and update statements to be mismatched and therefore enter an infinite loop):
CREATE FUNCTION upsert(sql_insert TEXT, sql_update TEXT) RETURNS VOID AS $$ DECLARE i integer := 0; BEGIN LOOP -- terminate if loop reaches 2 runs IF i >= 2 THEN RETURN; END IF; BEGIN -- try update EXECUTE sql_update; -- terminate if update successful IF found THEN RETURN; END IF; BEGIN -- try insert EXECUTE sql_insert; RETURN; -- insert violated unique constraint, try loop again EXCEPTION WHEN unique_violation THEN END; END; i := i+1; END LOOP; END; $$ LANGUAGE plpgsql;
The function works, tested it by creating a small table. So the problem must be with my input, but I still can't see what the issue is - unless the function is just incredibly slow. It's not a particularly big table with around 11,000 rows.
However, this sample input never finishes running:
SELECT upsert( $$ INSERT INTO accounts VALUES ( '0012000000wa7WzAAI', 'Random company', NULL, 'Tier 2', 'Joe BLoggs', 'Joe Bloggs', 'US', TIMESTAMP '2012-12-14 00:00:00', NULL, 'Ex-Customer') $$, $$ UPDATE accounts SET account_name='Random Companyh', country=NULL, account_tier='Tier 2', account_owner='Joe Bloggs', sales_person='Joe Bloggs', office_managed='US', date_first_opportunity=TIMESTAMP '2012-12-14 00:00:00', industry=NULL, account_type='Ex-Customer' WHERE account_id='0012000000wa7WzAAI' $$ )
I can't work out why this isn't working, any help would be appreciated.