Re: Update on existence?

Richard Broersma Jr wrote:
>> ------------------
>> select id, username, settings from sessions where id='foobar'
> this is handled using this link:
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ROWTYPES
> However, I seen it mentioned that there is a variable called FOUND that may be used for this also.
> It might be worth reading up on.
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-SQL-NORESULT
>
>> If a result was found
> this can be achieved from this link:
> http://www.postgresql.org/docs/8.2/interactive/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS

Many thanks! I asked on the IRC channel, and with some help there,
and your help here, I managed to put this together:

--------------------------------------
CREATE type sessrow AS (id char(32), addr inet, user_id integer, pubkey
bytea, blockkey bytea);

CREATE FUNCTION getsession(sid char(32)) RETURNS SETOF sessrow AS $$
DECLARE
reply sessrow%ROWTYPE;
BEGIN
SELECT s.id AS id,s.address AS addre,u.user_id AS user_id,s.pubkey
AS pubkey,s.blockkey AS blockkey
INTO reply
FROM sessions AS s
LEFT JOIN (SELECT sess_id,user_id FROM usersessions) AS u
ON s.id=u.sess_id
WHERE id=sid;
IF FOUND THEN
UPDATE sessions SET last_access=current_timestamp WHERE id=sid;
RETURN NEXT reply;
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
--------------------------------------

Guess what -- it works!

Well, almost. The query works, but when I run it, it seems like the
server using the query hangs after a while. At the moment, I don't know
who to blame though. :-)

When I try using my function from the command line interface, the
hang does not seem to occur.

One thought -- does my "UPDATE" line need to be in a transaction section?

--
Kind regards,
Jan Danielsson