I'm new to writing DB functions and I need to return the value of 'last_login_at' as OUT parameter when performing an UPDATE query.
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;
Is it possible to do multiple RETURNING x INTO y?
Can we use a CASE statement in RETURNING x INTO y?
I was able 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 at the point of RETURNING the last_login_at has already been updated so CASE always returns TRUE.
Is there a magical solution to my problem?
Is there a magical solution to my problem?
Actually, there is: Join to another instance of the "user" table in the FROM clause:
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;Now, the table alias u refers to the post-UPDATE state of the table, but u1 refers to a snapshot at the start of the query.
Detailed explanation:
Table-qualify all column references to be unambiguous, which is never a bad idea, but after the self-join it's required.
The manual about the short syntax EXIT WHEN FOUND.
You can use any expression in the RETURNING clause, including CASE statements. There just happens to be a simpler, cheaper way for this:
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 boolean result. If last_login_at is NULL, you get NULL.
Asides:
As for the rest of the query: expressions can be simplified, the LOOP is suspicious, you should never use reserved words as identifiers, even though double-quoting makes it possible ("user"
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments