'Postgresql: How to repeat query as soon as finished?

Let's say I have a query like so:

SELECT * FROM a WHERE a.Category = 'liquid' ORDER BY a.MeasurementTime DESC;

and I want to see the results coming into the database 'live'.

How can I write a query for Postresql which will repeat as soon as the query finishes?

postgresqlrepeat


Solution 1:[1]

You can use the \watch n command in the terminal to re-execute the query every n seconds.

Example:

postgre=# SELECT * FROM TABLE WHERE CONDITION
postgre=# \watch 5
-- now the "SELECT * FROM TABLE WHERE CONDITION" is re-executed every 5 seconds

Solution 2:[2]

You can't see them 'live'. Queries complete before returning to calling environment.

You could wrap this in a cron job ( depending on your environment ) or similar scheduler and have them run every minute, or a function and add that to pgagent to be run every minute.

To have a dml statement constantly running is not really a good idea and i would not recommend it for performance and table management purposes.

however...

Within a function you can create a loop with a wait clause using pg_sleep and just no break clause, but really a job is the best way to go.

Solution 3:[3]

watch -n1 'psql -h {ip} {db} {user} -c "select * from condition;"'

Make sure that you set the password of the {user} inside an environment variable:

Linux> export PGPASSWORD="password"

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Eric
Solution 2
Solution 3 Matthew Schinckel

Related Questions