I get the following error: SQL Error $[42702]$: ERROR: column reference "report$\_$date" is ambiguous

Detail: It could refer to either a PL$/$pgSQL variable or a table column.

Where: PL$/$pgSQL function get$\_$separated$\_$teams$($date$[])$ line $6$ at RETURN QUERY. $----------------------$ Here is my function: CREATE OR REPLACE FUNCTION get$\_$separated$\_$teams$($dates date$[])$

RETURNS TABLE$($

team$\_$a$\_$imei TEXT,

team$\_$b$\_$imei TEXT,

report$\_$date DATE,

Province TEXT,

District TEXT,

Tehsil TEXT,

Uc TEXT,

count$\_$distinct$\_$a$\_$ping$\_$time INT

$)$ AS $$

DECLARE

d DATE;

BEGIN

FOREACH d IN ARRAY dates LOOP

RETURN QUERY

WITH

dep$\_$rep AS $($

SELECT report $->$ 'details' AS details

FROM campaign$\_$deployment$\_$reporting cdr

$),$

format$\_$dep$\_$rep AS $($

SELECT json$\_$array$\_$elements$($details$)$::json AS first$\_$element

FROM dep$\_$rep

$),$

inter$\_$report AS $($

SELECT

first$\_$element::jsonb$->>$'IMEI' AS imei,

first$\_$element::jsonb$->>$'ReportingStart' AS reporting$\_$start,

first$\_$element::jsonb$->>$'ReportingEnd' AS reporting$\_$end,

first$\_$element::jsonb$->>$'Date' AS report$\_$date,

first$\_$element::jsonb$->>$'Province' AS Province,

first$\_$element::jsonb$->>$'District' AS District,

first$\_$element::jsonb$->>$'Tehsil' AS Tehsil,

first$\_$element::jsonb$->>\text{'}$Uc$\text{'}$ AS Uc

FROM format$\_$dep$\_$rep

$),$

final$\_$report AS $($

SELECT DISTINCT imei,

reporting$\_$start::timestamp,

reporting$\_$end::timestamp,

report$\_$date::date AS report$\_$date,

Province,

District,

Tehsil,

Uc

FROM inter$\_$report

WHERE report$\_$date::date $=$ d

$),$

pings AS MATERIALIZED $($

SELECT

u$.$imei AS team$\_$a$\_$imei,

a$.$imei AS team$\_$b$\_$imei,

u$.$t $+$ interval $\text{'}5$ hours' AS a$\_$ping$\_$time,

a$.$t $+$ interval $\text{'}5$ hours' AS b$\_$ping$\_$time

FROM $($

SELECT

p$.$imei,

st$\_$transform$($p$.$geoJson, $32643)$ AS geom,

generatedAt AS t$,$

p$.$ID AS u$\_$ping$\_$id

FROM campaign$\_$pings p

WHERE generatedAt $+$ interval $\text{'}5$ hours' BETWEEN d::timestamp $+$ interval $\text{'}06$:$00$:$00\text{'}$ AND d::timestamp $+$ interval $\text{'}18$:$00$:$00\text{'}$

$)$ u

LEFT JOIN $($

SELECT

p$.$imei,

p$.$ID AS a$\_$ping$\_$id$,$

st$\_$transform$($p$.$geoJson, $32643)$ AS geom,

generatedAt AS t

FROM campaign$\_$pings p

WHERE generatedAt $+$ interval $\text{'}5$ hours' BETWEEN d::timestamp $+$ interval $\text{'}06$:$00$:$00\text{'}$ AND d::timestamp $+$ interval $\text{'}18$:$00$:$00\text{'}$

$)$ a

ON NOT ST$\_$DWithin$($u$.$geom, a$.$geom, $300)$

AND a$.$t BETWEEN u$.$t $-$ INTERVAL $\text{'}30$ seconds' AND u$.$t $+$ INTERVAL $\text{'}30$ seconds'

WHERE a$.$imei IS NOT NULL

AND $($u$.$imei, a$.$imei$)$ IN $($SELECT team$\_$a$\_$imei, team$\_$b$\_$imei FROM separation$\_$table$)$

$),$

filters AS $($

SELECT

p$.*,$

fr$.$Province,

fr$.$District,

fr$.$Tehsil,

fr$.$Uc$,$

fr$.$reporting$\_$start AS reporting$\_$start$\_$a$,$

fr$.$reporting$\_$end AS reporting$\_$end$\_$a$,$

fr$1.$reporting$\_$start AS reporting$\_$start$\_$b$,$

fr$1.$reporting$\_$end AS reporting$\_$end$\_$b$,$

fr$.$report$\_$date

FROM pings p

LEFT JOIN final$\_$report fr ON fr$.$report$\_$date $=$ p$.$a$\_$ping$\_$time::date AND fr$.$imei $=$ p$.$team$\_$a$\_$imei

LEFT JOIN final$\_$report fr$1$ ON fr$1.$report$\_$date $=$ p$.$b$\_$ping$\_$time::date AND fr$1.$imei $=$ p$.$team$\_$b$\_$imei

WHERE $($

fr$.$reporting$\_$start IS NOT NULL

AND fr$.$reporting$\_$end IS NOT NULL

AND fr$1.$reporting$\_$start IS NOT NULL

AND fr$1.$reporting$\_$end IS NOT NULL

AND p$.$a$\_$ping$\_$time BETWEEN GREATEST$($fr$.$reporting$\_$start, fr$1.$reporting$\_$start$)$ AND LEAST$($fr$.$reporting$\_$end, fr$1.$reporting$\_$end$)$

$)$

$)$

SELECT

filters.team$\_$a$\_$imei,

filters.team$\_$b$\_$imei,

filters.report$\_$date,

filters.Province,

filters.District,

filters.Tehsil,

filters.Uc$,$

COUNT$($DISTINCT filters.a$\_$ping$\_$time$)$ AS count$\_$distinct$\_$a$\_$ping$\_$time

FROM filters

GROUP BY $1,2,3,4,5,6,7$

HAVING COUNT$(*)>30$;

END LOOP;

END;

$$ LANGUAGE plpgsql;