Re: Small change needed to support Greenplum DB

Luke Lonergan wrote:
> 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/27866354fa4d7a5e/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:


Hi Luke,

Does Greenplum support DISTINCT ON? Specifically,

SELECT j.*, cl.*, ag.*, sub.jlgstatus
FROM pgagent.pga_job j JOIN    pgagent.pga_jobclass cl ON cl.jclid=jobjclid LEFT OUTER JOIN    pgagent.pga_jobagent ag
ONag.jagpid=jobagentid JOIN    (SELECT DISTINCT ON (jlgjobid) jlgstatus, jlgjobid     FROM pgagent.pga_joblog     ORDER
BYjlgjobid, jlgid desc) sub ON sub.jlgjobid = j.jobid
 
--       + restriction +
ORDER BY jobname;

Regards, Dave.