plpgsql merge func question

Поиск
Список
Период
Сортировка
Искать
От
Matthias Leopold
Тема
plpgsql merge func question
Дата
Msg-id
52B436A9.3080100@aic.at
Список
Дерево обсуждения
plpgsql merge func question Matthias Leopold <matthias@aic.at>
Re: plpgsql merge func question Andreas Kretschmer <akretschmer@spamfence.net>
Re: plpgsql merge func question Merlin Moncure <mmoncure@gmail.com>
Re: plpgsql merge func question Matthias Leopold <matthias@aic.at>
Re: plpgsql merge func question Merlin Moncure <mmoncure@gmail.com>
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 по дате отправления
От: angelina410329
Дата:
От: Andreas Kretschmer
Дата:
FAQ