Thread: Update on existence?

Hello all,

   I have a table called sessions, with session ids. Each session also
has a last_access timestamp and some other data. Is is possible (using
PL/pgSQL) to implement something along the line of:

------------------
  select id, username, settings from sessions where id='foobar'
  If a result was found
     update sessions set last_access=current_timestamp where id='foobar'
------------------

   Obviously, I want to access the id, username and settings columns if
they are found.

   In other words: Is it possible to make something which behaves almost
like a normal "select", but which has some hidden internal magic as
described above?

--
Kind regards,
Jan Danielsson

> ------------------
>   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


>      update sessions set last_access=current_timestamp where id='foobar'
this is just about correct using the rowtype variable

I hope this helps,
Regards,
Richard Broersma Jr.

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

>         RETURN NEXT reply;

I think Return next is incorrect. try return reply;
>    One thought -- does my "UPDATE" line need to be in a transaction section?

by default it already is <I believe>.  All syntax in a function is executed inside a single
calling transaction.