Returning multiple values from an UPDATE query in PostgreSQL
I'm new to writing DB functions and I need to return the "last_login_at" value as a parameter OUT
when doing a query UPDATE
.
Here is a snippet of my function:
...
LOOP
UPDATE "user" SET
last_login_at = current_timestamp,
first_name = p_first_name,
last_name = p_last_name,
WHERE ext_user_id = p_ext_user_id AND platform_id = p_platform_id
RETURNING id INTO v_user_id;
is_new := false;
// The next 'CASE' is not valid - Need to replace it with a valid one.
has_logged_in_today = CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) > 1
THEN true
ELSE false
END;
IF FOUND THEN
EXIT;
END IF;
..
..
END LOOP;
Can you do multiple RETURNING x INTO y
?
Can we use the operator CASE
in RETURNING x INTO y
?
EDIT
I managed to get better results and now it looks like this:
...
LOOP
UPDATE "user" SET
login_consecutive_days = CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) > 1
THEN 0
ELSE login_consecutive_days + date_part('day', age(current_timestamp, last_login_at))
END,
login_max_consecutive_days = CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) = 1
AND (login_consecutive_days+1 > login_max_consecutive_days)
THEN login_consecutive_days+1
ELSE login_max_consecutive_days
END,
last_login_at = current_timestamp,
num_sessions = num_sessions + 1,
last_update_source = 'L',
first_name = p_first_name,
last_name = p_last_name,
additional_data = p_additional_data
WHERE ext_user_id = p_ext_user_id AND platform_id = p_platform_id
RETURNING id,
CASE
WHEN date_part('day', age(current_timestamp, last_login_at)) = 0
THEN true
ELSE false
END
INTO v_user_id, is_first_login_today;
is_new := false;
IF FOUND THEN
EXIT;
END IF;
...
The only problem with this is that the point RETURNING
last_login_at
has already been updated, so it CASE
always returns TRUE
.
Is there a magic solution to my problem?
source to share
Is there a magic solution to my problem?
Actually there is: Join another table instance "user"
in a sentence FROM
:
UPDATE "user" u
SET login_consecutive_days = ... -- unqualified column name
FROM "user" u1
WHERE u.ext_user_id = p_ext_user_id
AND u.platform_id = p_platform_id
AND u.id = u1.id -- must be unique not null (like the PK)
RETURNING u.id, (u1.last_login_at < now() + interval '1 day')
INTO v_user_id, is_first_login_today;
is_new := false;
EXIT WHEN FOUND;
The table alias now u
refers to the state of the post UPDATE
table, but u1
refers to the snapshot at the beginning of the query.
Detailed explanation:
Table - Qualify all column references to be unambiguous, which is never a bad idea, but after a self join is required.
Short Syntax Guide EXIT WHEN FOUND
.
You can use any expression in the sentence RETURNING
, including CASE
. It's just simpler and cheaper there:
CASE WHEN date_part('day', age(current_timestamp, last_login_at)) = 0
THEN true ELSE false END
Step 1:
CASE WHEN last_login_at < now() + interval '1 day'
THEN true ELSE false END
Step 2:
(last_login_at < now() + interval '1 day')
Just use the result boolean
. If last_login_at
there is NULL
, you get NULL
.
Asides:
Regarding the rest of the query: expressions can be simplified, LOOP
is suspicious, you should never use reserved words as identifiers, even though double quoting makes ( "user"
source to share
You can return multiple columns with the syntax:
UPDATE "user" SET
last_login_at = current_timestamp,
first_name = p_first_name,
last_name = p_last_name,
WHERE ext_user_id = p_ext_user_id AND platform_id = p_platform_id
RETURNING id, last_login_at
INTO v_user_id, v_login_at;
The return clause follows most of the rules for a SELECT list, so you can add as many columns as you like.
source to share