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