plpgsql merge func question

From: Matthias Leopold <matthias(at)aic(dot)at>
To: pgsql-novice(at)postgresql(dot)org
Subject: plpgsql merge func question
Date: 2013-12-20 12:23:05
Message-ID: 52B436A9.3080100@aic.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Andreas Kretschmer 2013-12-20 12:42:33 Re: plpgsql merge func question
Previous Message angelina410329 2013-12-18 08:18:55 Re: Recover Contacts from iPhone with iPhone Data Recovery