/* 
 * Bamboo version: 5.8+
 * Database: PostgreSQL
 * Triggers: 'Repository polling' > 'Polling strategy' > 'Scheduled'
 */
select B.FULL_KEY as PLAN_KEY,
       B.TITLE as TITLE,
       VL.PLUGIN_KEY as REPOSITORY_TYPE,
       unnest(xpath
        ('/configuration/triggers/triggerDefinition/pluginKey[text()="com.atlassian.bamboo.triggers.atlassian-bamboo-triggers:poll"]/../config/item/key[text()="repository.change.poll.type"]/../value[text()="CRON"]/../../item/key[text()="repository.change.poll.cronExpression"]/../value/text()',CAST(BD.XML_DEFINITION_DATA as XML)
       ))::text as SCHEDULED,
       BD.BUILD_DEFINITION_ID as BUILD_DEFINITION_ID
  from VCS_LOCATION as VL
  join PLAN_VCS_LOCATION as PVL on PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
  join BUILD as B on PVL.PLAN_ID = B.BUILD_ID
  join BUILD_DEFINITION as BD on B.BUILD_ID = BD.BUILD_ID
 where B.SUSPENDED_FROM_BUILDING = false
 order by SCHEDULED desc, PLAN_KEY, TITLE;