Database Reference
In-Depth Information
BEGIN
FOR o_status, o_message_text,
o_from_user, o_sent_at IN
UPDATE message
SET read_at = CURRENT_TIMESTAMP,
delivery_status = 'read'
WHERE to_user = i_username AND read_at
IS NULL
RETURNING 200, msg_body, from_user ,
sent_at
LOOP
RETURN NEXT;
END LOOP;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
We are almost done with the database part of our simple server. To finish it up, we
need to do some initial performance tuning, and for that we need some data in our
tables. The easiest way is to use the
generate_series()
function to generate a
list of numbers, which we will use as usernames. For our initial testing, names like 7
or 42 are as good as Bob, Mary, or Jill:
hannu=# SELECT new_user(generate_series::text,
'pwd', generate_series::text || '@pg.org')
hannu-# FROM generate_series(1,100000);
hannu=# WITH ns(n,len) AS (
hannu(# SELECT *,(random() * 10)::int
FROM generate_series(1,100000))
hannu-# SELECT
set_friends_list(ns.n::text,
hannu(# ARRAY( (SELECT
(random() * 100000)::int
hannu(# FROM
generate_series(1,len)) )::text[]
hannu(# )
hannu-# FROM ns ;
