hi,
i tried to write a merge function in plpgsql, which is derived from the
example in the docs (Example 38-2 in
http://www.postgresql.org/docs/8.4/static/plpgsql-control-structures.html).
Code is below. This works fine as long as entries in count_table have
todays date in "datum". when i have older entries the function "locks
up" (doesn't return, server has 100% cpu). i'm a plpgsql novice. can
someone explain why this happens? related question: i didn't find a way
to log queries that happen inside functions. is this possible?
postgres version is 8.4.17
thx
matthias
CREATE or replace FUNCTION merge_func(id INT, foobarvar text) RETURNS
integer AS
$$
DECLARE
countervar integer;
BEGIN
LOOP
UPDATE count_table ct
SET counter = counter+1 WHERE ct.user_id = id and
foobar = foobarvar and datum = current_date
returning counter into countervar;
IF found THEN
RETURN countervar;
END IF;
BEGIN
insert into count_table (user_id, foobar, datum,
counter) values (id, foobarvar, current_date, 1)
returning counter into countervar;
RETURN countervar;
EXCEPTION WHEN unique_violation THEN
END;
END LOOP;
END;
$$
LANGUAGE plpgsql;
\d count_table
Table "count_table"
Column | Type | Modifiers
---------+-----------------------+--------------------
user_id | integer | not null
foobar | character varying(30) | not null
datum | date | not null
counter | integer | not null default 0
