Skip site navigation (1) Skip section navigation (2)

Re: Update on existence?

From: Jan Danielsson <jan(dot)m(dot)danielsson(at)gmail(dot)com>
To: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Update on existence?
Date: 2007-04-05 03:47:30
Message-ID: 46147152.2090805@gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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


In response to

Responses

pgsql-novice by date

Next:From: Richard Broersma JrDate: 2007-04-05 04:42:38
Subject: Re: Update on existence?
Previous:From: Richard Broersma JrDate: 2007-04-05 02:56:26
Subject: Re: Update on existence?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group