CODEWITHSUNDEEP
sqlpostgresql
Illusionist
Illusionist

Reputation: 5509

Check if subscription is expired with postgres

I am creating a web app that will have some subscriptions. The backend is a postgres database with a user id and a date/time when their subscription expires.

Subscription Table
UserId SubscriptionExpiry
1      10/10/2020

I can write code that checks if subscription is expired and update the date/time as needed. Is there some built in postgres way to do this?

Upvotes: 0

Views: 1365

Answers (2)

Renato
Renato

Reputation: 2177

If you are on linux you can use a simple cron-based job scheduler for PostgreSQL (9.5 or higher): pg_cron.

It runs inside the database as an extension and uses the same syntax as regular cron, but it allows you to schedule PostgreSQL commands directly from the database:

-- Delete old data on Saturday at 3:30am (GMT)
SELECT cron.schedule('30 3 * * 6', $$DELETE FROM events WHERE event_time < now() - interval '1 week'$$);

 schedule
----------
    42

Upvotes: 2

Denis D.
Denis D.

Reputation: 148

I would suggest writing a postgre function that takes argument UserId and you can do all the logic (updating SubscriptionExpiry for example) you need in there. 

CREATE FUNCTION check_expired(attr type)
 RETURNS type AS
BEGIN
 -- logic
END;
LANGUAGE language_name;

You can call the function from the language you are using for web app simple as 

SELECT check_expired(1)

Upvotes: 1

Related Questions