plpgsql merge func question
От | Matthias Leopold |
---|---|
Тема | plpgsql merge func question |
Дата | |
Msg-id | 52B436A9.3080100@aic.at обсуждение исходный текст |
Ответы |
Re: plpgsql merge func question
|
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: