循環遍歷數組參數值以建構多個 WHERE 子句的函式（postgres 11.4）

我一直在迭代 Rob Conery 在他出色的文章中提出的一個想法，即使用 PostgreSQL Views 生成月度報告。

我的版本需要將視圖重構為函式，因為我需要使用輸入參數。我最近收到了一個添加過濾的請求，以便也可以搜尋特定的產品和位置，但我發現自己執行了 N 次此功能，這導致了嚴重的性能瓶頸。我認為在一個查詢中包含這些條件可以緩解這些性能問題。

在按照此處對一些問題的（寫得很好的）答案進行操作後，我取得了一些進展，但我仍然糾結於如何 WHERE為每個輸入數組元素生成子句。

基本上我想要的“輸出”SQL 看起來像這樣：

select sum(total) as total_activity, 
 count(1) as sales_event_count,
 created_at::date as sales_event_date,
 date_part('year',created_at at time zone 'hst') as year,
 date_part('quarter',created_at at time zone 'hst') as quarter,
 date_part('month',created_at at time zone 'hst') as month,
 date_part('day',created_at at time zone 'hst') as day
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
where (p.sku = '12345' and p.manufacturer = 'CompanyA' and location_id = 'LocationA') or 
     (p.sku = '09876' and p.manufacturer = 'CompanyA' and location_id = 'LocationA') or
     (p.sku = '10293' and p.manufacturer = 'CompanyB' and location_id = 'LocationA')
group by se.created_at
order by se.created_at  

以下是我為幫助解決此問題而探索的一些範例頁面：

• 將多個值集或數組傳遞給函式
• 如何將多個值選擇到一個數組中並循環？（postgres 9.3）
• 從 Postgres 中的多維數組參數中選擇

在從每一個中挑选和選擇之後，我想出了以下幾點：

create type product_type as(sku character varying(100), manufacturer character varying(200))

create or replace function find_sales_location_activity(
 _products_arr product_type[],
 _location_id bigint
)
returns table (total_activity bigint, sales_event_count bigint, sales_event_date date, "year" double precision, quarter double precision, "month" double precision, "day" double precision) as
$func$
select sum(total) as total_activity, 
 count(1) as sales_event_count,
 created_at::date as sales_event_date,
 date_part('year',created_at at time zone 'hst') as year,
 date_part('quarter',created_at at time zone 'hst') as quarter,
 date_part('month',created_at at time zone 'hst') as month,
 date_part('day',created_at at time zone 'hst') as day
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
where (p.sku = $1[1][1] and p.manufacturer = $1[1][2] and location_id = $2) or 
     (p.sku = $1[2][1] and p.manufacturer = $1[2][2] and location_id = $2) or
     (p.sku = $1[3][1] and p.manufacturer = $1[3][2] and location_id = $2)
group by se.created_at
order by se.created_at
$func$
language sql;

…但顯然這並沒有循環任何東西。我已經嘗試用替換FROM locations loc子句FROM generate_subscripts($1, 1)並嘗試以這種方式循環，但是替換錶名會導致我left outer join的 ’s 失敗。

顯然，我在這裡有點不知所措，但如果有人能引導我走向正確的方向，我會非常非常感激。提前致謝！

數組中的參數可以轉換為要與其餘參數連接的表。這種轉換是用unnest函式完成的，然後 WHERE 條件可以表示為 JOIN 子句。

這也可以通過加入來完成，generate_subscripts($1, 1)但這會導致更複雜的語法而沒有明顯的好處。

select ...<same as your query>...
from locations loc
left outer join sales_events se ON loc.id = se.location_id
left outer join junction_products jp ON jp.sales_event_id = se.id
left outer join products p ON p.id = jp.product_id
join unnest($1) params ON (p.sku=params.sku AND p.manufacturer=params.manufacturer)
WHERE location_id = $2
group by se.created_at
order by se.created_at

注意：此查詢left outer join保留原始查詢中的原樣，因為問題與它們無關，但似乎它們應該是內部聯接。原因是 WHERE 子句之類的 p.sku = '12345' and p.manufacturer = 'CompanyA' 暗示 p 中的這些列不能為 NULL，但這否定了您使用外連接與內連接的原因，內連接為每個表中的不匹配行添加了 NULL 列每個左連接的右側。

簡而言之，如果您不確定此查詢為何使用left outer join，請考慮將它們替換為join。

引用自：https://dba.stackexchange.com/questions/271599