Postgres function returns different row count the same query run by itself on Postgres console.
Please check the resultset values for both the below code blocks.
Query:
SELECT COALESCE( NULLIF(SUM(count_cc),NULL) , 0)::int as sum_cc FROM
(
SELECT COUNT(event_a_id)::int as count_cc
FROM
(
SELECT si, event_a_id, event_aa_id, count(event_a_id), date_part('year', date) as year, date_part('month', date) as month, date_part('day', date) as day, date_part('hour', date) as hour
from sch.tab1
where
( si = 4 )
and
(event_a_id::uuid = '53c69ab9-aaaa-bbbb-cccc-4a98cebf1092' )
and
event_aa_id::uuid = '62322dae-xxxx-yyyy-zzzz-87a5f61d3c4f'
and
(c1 is not null
and c1 <> ''
and c2 is not null
and c2 <> '')
and (date AT TIME ZONE 'America/Los_Angeles')::date = '2016-11-19'
GROUP BY si, event_a_id, event_aa_id, count(event_a_id), date_part('year', date), date_part('month', date), date_part('day', date), date_part('hour', date), date_part('minute', date)
having count(event_a_id) < 5
) as t1
GROUP BY si, event_a_id, event_aa_id, year, month, day, hour
having COUNT(event_a_id) < 10
) as t2
;
sum_cc -------- 11
Same query inside plpgsql function:
CREATE OR REPLACE FUNCTION schema.func(param1 int, param2 uuid, param_3 uuid, param4 character varying, param5 character varying, param6 date)
returns TABLE(sum_cc int) as $$
begin
RETURN QUERY
EXECUTE
'SELECT COALESCE( NULLIF(SUM(count_cc),NULL) , 0)::int as sum_cc FROM
(
SELECT COUNT(event_a_id)::int as count_cc
FROM
(
SELECT si, event_a_id, event_aa_id, count(event_a_id), date_part('year', date) as year, date_part('month', date) as month, date_part('day', date) as day, date_part('hour', date) as hour
from ' ||quote_ident(param5)||'.'||quote_ident(param4)||
' where
(si = $1 )
and
(event_a_id::uuid = $2 )
and
event_aa_id::uuid = $3
and
(c1 is not null
and c1 <> ''
and c2 is not null
and c2 <> '')
and (date AT TIME ZONE 'America/Los_Angeles')::date = $4
GROUP BY si, event_a_id, event_aa_id, count(event_a_id), date_part('year', date), date_part('month', date), date_part('day', date), date_part('hour', date), date_part('minute', date)
having count(event_a_id) < 5
) as t1
GROUP BY si, event_a_id, event_aa_id, year, month, day, hour
having COUNT(event_a_id) < 10
) as t2
' USING param1, param2, param3, param6 ;
end;
$$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
SELECT sum_ct FROM schema.func(4, '53c69ab9-aaaa-bbbb-cccc-4a98cebf1092', '62322dae-xxxx-yyyy-zzzz-87a5f61d3c4f', 'tab1', 'sch', '2016-11-19');
sum_cc -------- 14
What am I doing wrong?
Things I already tried that did not work:
- Changing plpgsql function from volatile to stable.
- Tried returning an integer variable instead of table.
- Searching stack overflow and google for an hour.
- Code review for syntax or semantic errors.
