execute insert statement only if a certain condition is met

Question

I'd like to run an insert statement only if a certain condition is met. Is there a way to do that in pure sql?

An example of what I'd like to do in pl/pgsql:

CREATE OR REPLACE FUNCTION set_search_lock (
    userid     IN   integer,
)
    RETURNS boolean AS $body$


DECLARE
BEGIN

    PERFORM 'A' FROM LOCK_TABLE WHERE USERID = userid LIMIT 1;

    IF NOT FOUND THEN
        INSERT INTO LOCK_TABLE (USERID) VALUES (userid);
        RETURN true;
    ELSE
        RETURN false;
    END IF;


END;
$body$
LANGUAGE PLPGSQL;

Like in this one: http://stackoverflow.com/questions/5288283/sql-server-insert-if-not-exists-best-practice ? — david a., Jul 13 '12 at 09:44
Pure SQL = ANSI standard SQL, no procedural language extensions. — Mihai Stancu, Jul 13 '12 at 09:47

score 3 · Accepted Answer · answered Jul 13 '12 at 09:51

3

Assuming you want to insert the value 42, then the following will only insert a row if there isn't one already:

insert into lock_table (userid) 
select 42
where not exists (select 1 from lock_table where userid = 42);

answered Jul 13 '12 at 09:51

a_horse_with_no_name

440,273
77
685
758

execute insert statement only if a certain condition is met

1 Answers1