Using one query in a function to fill the variables of another query

Question

I'm trying to pull a list of #'s (partner_id) from a table dwh.sgmt_clients and then insert those numbers into the large query in my function one by one where the variable $1 is located. Then I want them to show up as a query. I can't for the life of me figure it out

CREATE OR REPLACE FUNCTION dwh.cu_summary_function()
 RETURNS setof record AS
$$
declare partner_id ALIAS FOR $1
BEGIN
RETURN
select a.partner_id from dwh.sgmt_clients a 
loop
RETURN query
  select
avg(klicount)::int as average_kli, min(klicount) as min_kli, max(klicount) as max_kli,
(select count(kli) from ad_delivery.sgmt_kli_adic a where a.partner_id = $1) as total_kli, 
(select count(distinct(kli))from ad_delivery.sgmt_kli_adic a where a.partner_id = $1) as total_distinct_kli, a.partner_id
from (
select adic, count(kli) as klicount, x.partner_id
from ad_delivery.sgmt_kli_adic x
where x.partner_id = $1
group by adic, x.partner_id
) as a
group by a.partner_id;
end loop
END
$$
 LANGUAGE plpgsql VOLATILE
 COST 100;

You deleted and recreated the question instead of improving it. I had provided a fully functional query. Decent work gone to waste, I consider myself warned. — Erwin Brandstetter
– Erwin Brandstetter, Commented May 22, 2012 at 22:16

Pavel Stehule · Accepted Answer · 2012-05-22 19:37:43Z

2

CREATE OR REPLACE FUNCTION dwh.cu_summary_function(int) --<<<<<<<<<
RETURNS setof record AS

This parameter definition is little bit obsolete, use:

CREATE OR REPLACE FUNCTION dwh.cu_summary_function(partner_id int)
RETURNS setof record AS

answered May 22, 2012 at 19:37

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using one query in a function to fill the variables of another query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related