How to declare a variable in PostgreSQL queries

Question

I'm trying and struggling to declare variables in PostgreSQL queries. Can anyone help me with the below?

declare CampaignID val INT;
select CampaignID = 6
select
    result_code.description,
    count (*) as count
from 
    history
        left join result_code on result_code.result_code = history.result_code
where
    campaign_id = CampaignID
        and history.start_date_time between '2016-12-06 00:00:00' and '2016-12-06 23:00:00'
group by
    result_code.description

Answer 1

1) First way (with PL/pgSQL function):

CREATE OR REPLACE FUNCTION MY_SELECT_FUNC(CampaignID integer)
RETURNS TABLE(res_description character varying(255), res_count integer) AS
$BODY$
BEGIN

  for res_description, res_count
  in
  select
    result_code.description,
    count (*) as count
  from history
    left join result_code on result_code.result_code = history.result_code
  where campaign_id = CampaignID
    and history.start_date_time between '2016-12-06 00:00:00' and '2016-12-06 23:00:00'
  group by result_code.description
  loop
    return next;
  end loop;

END;$BODY$
LANGUAGE plpgsql VOLATILE;

and then you can select result in sql:

SELECT * from MY_SELECT_FUNC(6);

2) Second way (with sql function):

CREATE TYPE MY_SELECT_FUNC_RES AS (res_description character varying(255), res_count integer);

CREATE OR REPLACE FUNCTION MY_SELECT_FUNC(CampaignID integer)
RETURNS SETOF MY_SELECT_FUNC_RES AS
$$
  select
    result_code.description,
    CAST(count(*) AS INTEGER)
  from history
    left join result_code on result_code.result_code = history.result_code
  where campaign_id = CampaignID
    and history.start_date_time between '2016-12-06 00:00:00' and '2016-12-06 23:00:00'
  group by result_code.description
$$
LANGUAGE SQL;

and then you can select result in sql:

SELECT * from MY_SELECT_FUNC(6);