[pgadmin-support] Small change needed to support Greenplum DB

[Luke Lonergan]

Hi all,

A customer of ours is using pgAgent with Greenplum Database and needs a
change to one of the queries used by pgAgent to allow it to work with
Greenplum.

Here is the conversation:
  
http://groups.google.com/group/pgsql.bugs/browse_thread/thread/27866354fa4d7
a5e/60e6e5de3c5156d4#60e6e5de3c5156d4

The problem is that Greenplum does not support some correlated subqueries,
so Jon created a version of the query that is both faster and does not use a
correlated subquery.

The requested change is from this in pgaJob.cpp:

SELECT *, 
           (SELECT jlgstatus FROM pgagent.pga_joblog jl WHERE jl.jlgjobid =
j.jobid ORDER BY jlgid DESC LIMIT 1) AS joblastresult
           FROM pgagent.pga_job j
         JOIN pgagent.pga_jobclass cl ON cl.jclid=jobjclid
         LEFT OUTER JOIN pgagent.pga_jobagent ag ON ag.jagpid=jobagentid
--       + restriction +
        ORDER BY jobname;

To this:

select j.*, cl.*, ag.*, sub3.jlgstatus
   from pgagent.pga_job j join
        pgagent.pga_jobclass cl on cl.jclid=jobjclid left outer join
        pgagent.pga_jobagent ag on ag.jagpid=jobagentid
        join (select j2.jlgstatus,
                     sub.jlgjobid
                from pgagent.pga_joblog j2 join
                     (select jl.jlgjobid,
                             max(jl.jlgid) as max_jlgid
                        from pgagent.pga_joblog jl
                       group by jl.jlgjobid) sub
                     on sub.jlgjobid = j2.jlgjobid and
                        sub.max_jlgid = j2.jlgid) sub3
        on sub3.jlgjobid = j.jobid
--       + restriction +
order by jobname;

- Luke