Database Reference
In-Depth Information
UPDATE job_queue SET processed = TRUE WHERE
id = q.id;
END LOOP;
The previous example shows a basic strategy pattern of processing messages in a
job queue. Using this technique, rows in a table contain a list of jobs that need to be
processed.
We introduce the
EXECUTE
statement here, too. The
SELECT
statement is a string
value. Using
EXECUTE
, we can dynamically build PL/pgSQL commands as strings
and then invoke them as statements against the database. This technique comes in
handy when we want to change the table name or other SQL keywords that make
up our statement. These parts of the SQL statement cannot be stored in variables,
and are not generally "changeable". With
EXECUTE
, we can change any part of the
statement we jolly well please.
The following is an example that comes from the PostgreSQL documentation that
shows dynamic commands running inside of a loop:
CREATE FUNCTION cs_refresh_mviews() RETURNS
integer AS $$
DECLARE
mviews RECORD;
BEGIN
PERFORM cs_log('Refreshing materialized
views...');
FOR mviews IN SELECT * FROM
