Can a plpgsql stored procedure monitor for a query and kill it?
[Originally, the question was tagged with PostgreSQL.  I have no idea about Greenplum, but hopefully someone will turn up to answer for that system, too.  The rest applies to PostgreSQL only (8.3 onwards, whereas Greenplum was forked from 8.2).]

There is a relevant snippet in the documentation:


  Another important point is that when a server process is asked to display any of these statistics, it first fetches the most recent report emitted by the collector process and then continues to use this snapshot for all statistical views and functions until the end of its current transaction.


That means that the output is frozen for the transaction that once queries pg_stat_activity.  Fortunately, a few sentences later, this is being said:


  Alternatively, you can invoke pg_stat_clear_snapshot(), which will discard the current transaction's statistics snapshot (if any). The next use of statistical information will cause a new snapshot to be fetched.


I've tried it in the below minimalistic example, while issuing different statements in an other session:

DO $$ 
DECLARE i text; 
BEGIN 
    LOOP 
        PERFORM pg_stat_clear_snapshot(); -- makes the output up-to-date
        PERFORM pg_sleep(1);  

        FOR i IN SELECT query FROM pg_stat_activity 
        LOOP 
            RAISE WARNING '%', i; 
        END LOOP; 
    END LOOP; 
END;$$;


It finds newly issued queries and even new sessions, so I think this is what you need.