Обсуждение: FW: INSERT WHERE NOT EXISTS

> // check if entry already exists
> SELECT COUNT(*) FROM tablename WHERE [cond]
> ..
> if($count >0)
>   UPDATE
> else
>   INSERT
>
> but this will double the hit to the database server, because
> for every
> operation I need to do SELECT COUNT(*) first. The data itself
> is not a lot,
> and the condition is not complex, but the hitting frequency is a lot.

Why not use plpgsql?

CREATE FUNCTION a_test(int4) RETURNS int AS '
DECLARE
    totest    int;
BEGIN
    SELECT INTO totest <ID> FROM <table> WHERE <ID> = $1;

    IF totest IS null THEN
        -- do insert.
        return 1;
    ELSE
        -- do update.
        return 0;
    END IF;
END;
' language 'plpgsql';

For efficiency make sure ID is a index...