array_to_json - dealing with returning no rows

Hi all,

In my program, I generate SQLs from definitions, an example is:

(define-db-resource Event
[{:oid {:type :bigserial :primary-key true}}
{:name {:type :text :not-null true}}
{:tour-oid {:type :bigint :not-null true :references [Tour :oid]}}
{:tour {:type :join :join-info {:home-key :tour-oid
:foreign-key :oid :join-resource Tour :foreign-columns [:oid :name]
:singular? true}}}
{:campaigns {:type :join :join-info {:home-key :oid :foreign-key
:event-oid :join-resource Campaign
:foreign-columns [:oid :type :name]}}}])

From definitions my code generate various SQLs and this is a 'select'
example for the above definition:

SELECT event.oid,event.name,
ROW_TO_JSON((SELECT sj_tour FROM (SELECT j_tour.oid,
j_tour.name) sj_tour)) AS tour,
ARRAY_TO_JSON(ARRAY_AGG((SELECT sj_campaigns FROM (SELECT
j_campaign.oid,j_campaign.name,j_campaign.type WHERE j_campaign.oid IS
NOT NULL) sj_campaigns)) AS campaigns
FROM event LEFT OUTER JOIN tour AS j_tour ON tour_oid = j_tour.oid
LEFT OUTER JOIN campaign AS j_campaign ON event.oid
= j_campaign.event_oid
GROUP BY event.oid, j_tour.oid;

The problem I have is getting '[null]' as ARRAY_TO_JSON result when
there is no rows.

Ideally, I want to get '[]' or null for no rows or '[{...some JSON keys
and values ...},{ ... more ... }]' for some rows.

Also any suggestions will be great with above example query.

Thanks!

- Jong-won

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general