In the below query I am not able to replace the variable :v7. Please help me resolve this.

select
(case when donor='1' then 'ABC' when donor='2' then 'DEF' when donor='3' then 'GHI' else 'OTHER' END) as "MSP",
'DP' as "Role",
'Service' as "Transaction",
current_date as "Date",
coalesce(sum(case when c.data='1' then cnt end),0) as "Total Count",
coalesce(sum(case when c.data='2' then cnt end),0) as "Counta in :v7 or less",
coalesce(sum(case when c.data='3' then cnt end),0) as "Counta delivered in >:v7",
coalesce(round(sum(case when c.data='2' then cnt end)/sum(case when c.data='1' then cnt end)*100,2),0)||'%' as "Service Level"
from
(
select '1' as data,count(*) as cnt,donor from tbl_portorder a, tbl_portsubscriber b where a.rid=b.npo_rid  and npo_type='NOR' and send_sd_time::date between DATE((:v1)::TEXT) and DATE((:v2)::TEXT) and sd_rsp_time is not null group by 1,3
union all
select '1' as data,count(*) as cnt,donor from tbl_portorder_history a,tbl_portsubscriber_history b where a.rid=b.npo_rid  and npo_type='NOR' and send_sd_time::date between DATE((:v1)::TEXT) and DATE((:v2)::TEXT) and sd_rsp_time is not null group by 1,3
union all
select '2' as data,count(msisdn) as cnt,donor from tbl_portorder a, tbl_portsubscriber b where a.rid=b.npo_rid  and npo_type='NOR' and send_sd_time::date between DATE((:v1)::TEXT) and DATE((:v2)::TEXT) and sd_rsp_time is not null and sd_rsp_time between send_sd_time and send_sd_time interval :v6 group by 1,3
union all
select '2' as data,count(msisdn) as cnt,donor from tbl_portorder_history a,tbl_portsubscriber_history b where a.rid=b.npo_rid  and npo_type='NOR' and send_sd_time::date between DATE((:v1)::TEXT) and DATE((:v2)::TEXT) and sd_rsp_time is not null and sd_rsp_time between send_sd_time and send_sd_time interval :v6 group by 1,3
union all
select '3' as data,count(msisdn) as cnt,donor from tbl_portorder a, tbl_portsubscriber b where a.rid=b.npo_rid  and npo_type='NOR' and send_sd_time::date between DATE((:v1)::TEXT) and DATE((:v2)::TEXT) and sd_rsp_time is not null and sd_rsp_time > send_sd_time interval :v6 group by 1,3
union all
select '3' as data,count(msisdn) as cnt,donor from tbl_portorder_history a,tbl_portsubscriber_history b where a.rid=b.npo_rid  and npo_type='NOR' and send_sd_time::date between DATE((:v1)::TEXT) and DATE((:v2)::TEXT) and sd_rsp_time is not null and sd_rsp_time > send_sd_time interval :v6 group by 1,3
) c group by 1,2;

CodePudding user response：

It will show as an alias to the column as per your query, what exactly do you want to change, plz elaborate on it.

enter image description here

CodePudding user response：

The question could be rephrased as: how to use a psql variable as part of a column name?

It can be done in two steps:

1. create another variable with \set to assemble the other variable with the rest of the contents of the column name. The quoting rules inside psql metacommands are tricky so you need to be careful with spaces and quotes.

2. use this variable as your column alias:

   SELECT [expression] AS :"mycolvar", ...

Example :

$ psql -v v7="multi-word string" << EOF
\set colname 'Counta in ' :v7 ' or less'
select 'example' as :"colname";
EOF

Result:

 Counta in multi-word string or less 
-------------------------------------
 example