CODEWITHSUNDEEP
sqldatabasepostgresql
user2343714
user2343714

Reputation: 33

SQL change rule based off when a time happens

I am using postgresql is it possible for me to change a table when a specific time happens. I would like to modify values when a specific date happens that is specified in the table being modified. For example:

a piece of artwork is located in a museum, after it's exhibition ends it is automatically placed back into storage, changing its location attribute. This occurs on a specified date.

Upvotes: 0

Views: 56

Answers (3)

Szocske
Szocske

Reputation: 7661

Maybe you need a different data model, one that would allow you to store "historical" location. (in quotes because you'll keep future records there, too.)

Upvotes: 0

Denis de Bernardy
Denis de Bernardy

Reputation: 78413

It is not possible. See cron jobs, as suggested by Muleinik... but then, to expand on your example:

a piece of artwork is located in a museum, after it's exhibition ends it is automatically placed back into storage, changing its location attribute. This occurs on a specified date.

What happens if the piece of art is stolen (happens), or the museum it got sent to as part of a temporary exhibition decides to keep it (happens) or return it to its "rightful owner" (happens), or it's shelved in the wrong location (happens), etc.?

Don't just assume that things will go well -- they won't.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311123

postgres does not have triggers on system-wide event (such as time). What you can do, however, is have the OS's cron or at services do it for you by scheduling a statement like this:

echo "UPDATE artwork SET location='storage' WHERE name='Mona Lisa' | psql -u some_user -d some_database

Upvotes: 0

Related Questions