-----------------------------------------------------------------------------
-- fn_available_campaigns(site_id, ad_type, action_type, address)

CREATE OR REPLACE FUNCTION fn_available_campaigns(INTEGER, TEXT, TEXT, INET)
RETURNS SETOF campaigns AS $PROC$

  SELECT campaigns.*
    FROM campaigns
    INNER JOIN subscriptions
      ON subscriptions.campaign_id = campaigns.id
     AND subscriptions.site_id = $1
    WHERE campaigns.ad_type = $2

  -- exclude banned campaigns
  EXCEPT
  SELECT campaigns.*
    FROM campaigns
    INNER JOIN actions
      ON campaigns.id = actions.campaign_id
     AND campaigns.pay_per = $3
     AND actions.compensated
     AND actions.address = $4
     AND now() - actions.created_at < campaigns.reload_ban;

$PROC$ LANGUAGE SQL;
-----------------------------------------------------------------------------
-- fn_action()

CREATE OR REPLACE FUNCTION fn_action(
  v_action_id IN INTEGER,
  v_site_id   IN INTEGER,
  v_action    IN TEXT,    -- (view, click, lead, sale)
  v_ad_type   IN TEXT,    -- (banner, textlink, ...)
  v_format_id IN INTEGER, -- may be NULL
  v_address   IN INET
) RETURNS TEXT AS $PROC$

DECLARE
  a_compensated BOOLEAN;
  a_pay_per TEXT;
  a_campaign_id INTEGER;
  a_charge NUMERIC(12,6);
  a_return TEXT;

...

      SELECT campaigns.id
        INTO a_campaign_id
        FROM (
          SELECT campaigns.id
            FROM fn_available_campaigns(v_site_id, v_ad_type, v_action, v_address)
              AS campaigns

          -- include only campaigns with banners of the given format
          INTERSECT
          SELECT DISTINCT ON(banners.campaign_id) campaigns.id
            FROM campaigns
            INNER JOIN banners
              ON banners.campaign_id = campaigns.id
             AND banners.format_id = v_format_id
        ) AS campaigns
        ORDER BY random() LIMIT 1;