Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CREATE OR REPLACE FUNCTION update_user_status_with_old_status_checked(user_id_ integer, status_old_ status, status_new_ status, check_old_status_ boolean)
RETURNS SETOF lon_lat_user AS
$$
DECLARE
BEGIN
RETURN QUERY
SELECT (user_to_lon_lat_user(users_full.*, 0)).*
FROM users u
JOIN users_full ON users_full.id = u.id
WHERE u.id = user_id_
AND CASE WHEN check_old_status_ THEN u.status = status_old_ ELSE true END
FOR UPDATE OF u;
IF FOUND THEN
UPDATE users
SET status = status_new_,
updated_time = current_timestamp AT time zone 'UTC'
WHERE id = user_id_ AND status NOT IN ('inactivated', 'deleted');
IF FOUND THEN
RETURN QUERY
SELECT (user_to_lon_lat_user(users_full.*, 0)).*
FROM users_full
WHERE id = user_id_;
END IF;
END IF;
END
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION find_whether_user_is_reported(user_id_ integer)
RETURNS SETOF BOOLEAN AS
$$
DECLARE
BEGIN
RETURN QUERY
SELECT EXISTS (SELECT * FROM reported_users WHERE user_id = user_id_ AND status = 'default');
END
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION select_user_ids_sharing_same_main_picture(user_id_ integer) RETURNS integer[] AS
$$
DECLARE
uids_ integer[];
BEGIN
WITH shared_picture_uids AS (
SELECT p2.user_id
FROM user_pictures p1
JOIN user_pictures p2
ON p1.user_id != p2.user_id
AND p1.picture_dhash != 0
AND p1.status != 'deleted'
AND p1.index = 0
AND p1.picture_dhash = p2.picture_dhash
WHERE p1.user_id = user_id_
LIMIT 20 -- protection against pictures shared among too many users
), to_update as (
SELECT * FROM shared_picture_uids
UNION
SELECT user_id_ WHERE EXISTS (SELECT 1 FROM shared_picture_uids)
)
SELECT ARRAY_AGG(user_id) INTO uids_ FROM to_update;
RETURN uids_;
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION select_whether_user_updated_gender(user_id_ integer) RETURNS BOOLEAN AS
$$
DECLARE
res_ boolean;
BEGIN
SELECT EXISTS INTO res_(
SELECT 1
FROM suspicious_user_events
WHERE user_id = user_id_
AND status = 'default'
AND event = 'user.updatedGenderCount'
AND score > 0
);
RETURN res_;
END;
$$ LANGUAGE PLPGSQL;
CREATE OR REPLACE FUNCTION select_whether_user_suspicious(user_id_ integer) RETURNS BOOLEAN AS
$$
DECLARE
res_ boolean;
BEGIN
SELECT EXISTS INTO res_(
SELECT 1
FROM suspicious_user_events e
JOIN users u ON u.id = e.user_id
WHERE e.event IN ('user.jailbroken', 'user.suspiciousAndroidDevice')
AND e.user_id = user_id_
AND e.status = 'default'
AND u.gender = 'female'
);
RETURN res_;
END;
$$ LANGUAGE PLPGSQL;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.