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: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Richard Broersma Jr wrote:
>> ------------------
>>   select id, username, settings from sessions where id='foobar'
> this is handled using this link:
> 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. 
>>   If a result was found
> this can be achieved from this link:

   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 $$
    reply sessrow%ROWTYPE;
    SELECT 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
    WHERE id=sid;
        UPDATE sessions SET last_access=current_timestamp WHERE id=sid;
        RETURN NEXT reply;
    END IF;
$$ 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


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-2017 The PostgreSQL Global Development Group