h345k34cr h345k34cr - 3 months ago 24
SQL Question

plpgsql operate on query before returning it

I'd like to write a plpgsql procedure that executes a select query, updates the row (if existing) and then returns the result.

For that I'd like to be able to handle the procedure call like a normal query with

SELECT nickname FROM use_session_token(...)
.

Usually I would use
RETURN QUERY(...)
but I want to update the row first (it's either one or none because
token
is a primary index)

But in fact I want the row only returned and updated if other criteria are fulfilled, so I can't just operate on the primary key itself.

I had two attempts, one using a
Refcursor
and the other one using
SELECT INTO
, but I fail to actually return a
SETOF users
.

My attempt with
SELECT INTO
:

CREATE OR REPLACE FUNCTION use_session_token(Char(128), Inet) RETURNS SETOF users AS $$
DECLARE
row Record;
BEGIN
SELECT u.* INTO row FROM sessions AS t
INNER JOIN users AS u ON (t.user_id = u.id)
WHERE
t.token=$1 AND
t.date_last_used > NOW() - interval '30 minutes' AND
t.ip_address=$2 AND
u.is_deleted=FALSE AND
EXISTS(
SELECT 1 FROM mail AS m
WHERE m.user_id=u.id AND m.is_confirmed=TRUE AND m.is_deleted=FALSE
)
;

IF (row) THEN
UPDATE sessions SET date_last_used=NOW() WHERE token=$1;
ELSE
-- maybe do other things if there is no result
END IF;

RETURN row;
END;
$$ LANGUAGE 'plpgsql';


My attempt with
cursor
:

CREATE OR REPLACE FUNCTION use_session_token(Char(128), Inet) RETURNS SETOF users AS $$
DECLARE
cursor Refcursor;
row Record;
BEGIN
OPEN cursor SCROLL FOR (
SELECT u.* INTO row FROM sessions AS t
INNER JOIN users AS u ON (t.user_id = u.id)
WHERE
t.token=$1 AND
t.date_last_used > NOW() - interval '30 minutes' AND
t.ip_address=$2 AND
u.is_deleted=FALSE AND
EXISTS(
SELECT 1 FROM mail AS m
WHERE m.user_id=u.id AND m.is_confirmed=TRUE AND m.is_deleted=FALSE
)
);

FETCH cursor INTO row;

IF (FOUND) THEN
MOVE PRIOR cursor;
UPDATE sessions SET date_last_used=NOW() WHERE CURRENT OF cursor;
ELSE
-- maybe do other things if there is no result
END IF;

RETURN row;
END;
$$ LANGUAGE 'plpgsql';


But both attempts actually fail with the fact that I'm not really able to return a proper result set.

What would be the best way to accomplish that and to solve my problem?

And then, which of the both attempts is the better one (or is a third solution better)?

Answer

Your first attempt was the best (cursors tend to be slow), but you should use RETURN NEXT to return any rows from the function. With some other improvements, you get this:

CREATE OR REPLACE FUNCTION use_session_token(Char(128), Inet) RETURNS SETOF users AS $$
DECLARE
  rec users%rowtype; -- don't use reserved word as variable name, use explicit type
BEGIN
  SELECT u.* INTO rec FROM sessions AS t
  JOIN users AS u ON t.user_id = u.id
  WHERE t.token=$1
    AND t.date_last_used > now() - interval '30 minutes'
    AND t.ip_address=$2
    AND NOT u.is_deleted
    AND EXISTS (
      SELECT 1 FROM mail AS m
      WHERE m.user_id=u.id AND m.is_confirmed AND NOT m.is_deleted;

  IF FOUND THEN  -- use built-in parameter to test for result of query
    UPDATE sessions SET date_last_used = now() WHERE token=$1;
  ELSE
    -- maybe do other things if there is no result
  END IF;

  RETURN NEXT rec;
END;
$$ LANGUAGE 'plpgsql';

If you return the selected row irrespective of what happens after the SELECT query (the IF FOUND THEN ... section), then you can even forget about the rec variable and write the first statement as:

RETURN NEXT QUERY SELECT u.* ...

Note that RETURN NEXT QUERY does not actually return from the function, it merely adds data to the result set.