Scribd
Upload
9 views1 page

PostgreSQL User Login Tracking Setup

The document outlines the creation of a PostgreSQL table named 'user_login' to store usernames and their last login timestamps. It includes a PL/pgSQL function 'update_last_login' that updates the last login timestamp or inserts a new record if the user does not exist. Additionally, a trigger 'update_last_login_trigger' is created to execute this function whenever a user logs in, and a sample query is provided to retrieve the last login for a specific username.

Uploaded by

droppdrill
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
9 views1 page

PostgreSQL User Login Tracking Setup

The document outlines the creation of a PostgreSQL table named 'user_login' to store usernames and their last login timestamps. It includes a PL/pgSQL function 'update_last_login' that updates the last login timestamp or inserts a new record if the user does not exist. Additionally, a trigger 'update_last_login_trigger' is created to execute this function whenever a user logs in, and a sample query is provided to retrieve the last login for a specific username.

Uploaded by

droppdrill
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

create a table to store the login information

CREATE TABLE user_login (


username VARCHAR(50) PRIMARY KEY,
last_login TIMESTAMP
);

Create a PL/pgSQL function that updates the last login timestamp whenever a user
logs in

CREATE OR REPLACE FUNCTION update_last_login()


RETURNS TRIGGER AS $$
BEGIN
-- Update the last login timestamp for the logged-in user
UPDATE user_login
SET last_login = NOW()
WHERE username = current_user;

-- If the user doesn't exist in the table, insert a new record


IF NOT FOUND THEN
INSERT INTO user_login (username, last_login)
VALUES (current_user, NOW());
END IF;

RETURN NULL;
END;
$$ LANGUAGE plpgsql;

Next, create a trigger that fires the update_last_login function whenever a user
logs in.

CREATE TRIGGER update_last_login_trigger


AFTER LOGON ON DATABASE
EXECUTE FUNCTION update_last_login();

Try

SELECT last_login
FROM user_login
WHERE username = 'desired_username';

You might also like