Postgresql

在 Postgres 函式中，如果輸入值為空，如何跳過表達式？

  • July 22, 2020

我有一個工作功能。但它只有在我同時提供城市和國家變數時才有效。

CREATE OR REPLACE FUNCTION gridref_by_address(city text, country text)
RETURNS SETOF research_db_gridreference AS $$
 SELECT id, data, version
 FROM research_db_gridreference, jsonb_array_elements(data -> 'addresses') addresses
 WHERE (
   addresses ->> 'city' = city
   AND
   addresses ->> 'country' = country
 )
$$ LANGUAGE sql STABLE;

這將返回數據，例如：

| id            | data                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    | version    |
|---------------|---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|------------|
| grid.411921.e | {"id": "grid.411921.e", "name": "Cape Peninsula University of Technology", "links": ["http://www.cput.ac.za/"], "types": ["Education"], "labels": [{"label": "Kaapse Skiereiland Universiteit van Tegnologie", "iso639": "af"}], "status": "active", "weight": 1, "aliases": [], "acronyms": [], "addresses": [{"lat": -33.932222, "lng": 18.640278, "city": "Cape Town", "state": null, "line_1": "", "line_2": "", "line_3": null, "country": "South Africa", "primary": false, "postcode": "", "state_code": "", "country_code": "ZA", "geonames_city": {"id": 3369157, "city": "Cape Town", "license": {"license": "http://creativecommons.org/licenses/by/3.0/", "attribution": "Data from geonames.org under a CC-BY 3.0 license"}, "nuts_level1": null, "nuts_level2": null, "nuts_level3": null, "geonames_admin1": {"code": "ZA.11", "name": "Western Cape", "ascii_name": "Western Cape"}, "geonames_admin2": {"code": "ZA.11.CPT", "name": "City of Cape Town", "ascii_name": "City of Cape Town"}}}], "established": 2005, "external_ids": {"ROR": {"all": ["https://ror.org/056e9h402"], "preferred": "https://ror.org/056e9h402"}, "ISNI": {"all": ["0000 0001 0177 134X"], "preferred": null}, "OrgRef": {"all": ["3197244"], "preferred": null}, "FundRef": {"all": ["501100004512"], "preferred": null}, "Wikidata": {"all": ["Q1034468"], "preferred": null}}, "ip_addresses": [], "email_address": null, "relationships": [], "wikipedia_url": "http://en.wikipedia.org/wiki/Cape_Peninsula_University_of_Technology"}               | 2020-03-15 |
| grid.413335.3 | {"id": "grid.413335.3", "name": "Groote Schuur Hospital", "links": ["https://www.westerncape.gov.za/your_gov/163"], "types": ["Healthcare"], "labels": [], "status": "active", "weight": 1, "aliases": [], "acronyms": [], "addresses": [{"lat": -33.941, "lng": 18.463, "city": "Cape Town", "state": null, "line_1": "", "line_2": "", "line_3": null, "country": "South Africa", "primary": false, "postcode": "", "state_code": "", "country_code": "ZA", "geonames_city": {"id": 3369157, "city": "Cape Town", "license": {"license": "http://creativecommons.org/licenses/by/3.0/", "attribution": "Data from geonames.org under a CC-BY 3.0 license"}, "nuts_level1": null, "nuts_level2": null, "nuts_level3": null, "geonames_admin1": {"code": "ZA.11", "name": "Western Cape", "ascii_name": "Western Cape"}, "geonames_admin2": {"code": "ZA.11.CPT", "name": "City of Cape Town", "ascii_name": "City of Cape Town"}}}], "established": 1938, "external_ids": {"ROR": {"all": ["https://ror.org/00c879s84"], "preferred": "https://ror.org/00c879s84"}, "ISNI": {"all": ["0000 0004 0635 1506"], "preferred": null}, "OrgRef": {"all": ["840500"], "preferred": null}, "Wikidata": {"all": ["Q368400"], "preferred": null}}, "ip_addresses": [], "email_address": null, "relationships": [{"id": "grid.7836.a", "type": "Related", "label": "University of Cape Town"}, {"id": "grid.467135.2", "type": "Parent", "label": "Western Cape Department of Health"}], "wikipedia_url": "https://en.wikipedia.org/wiki/Groote_Schuur_Hospital"} | 2020-03-15 |

我希望能夠做類似的事情：

CREATE OR REPLACE FUNCTION gridref_by_address(city text default null, state text default null, country text default null)
RETURNS SETOF research_db_gridreference AS $$
 SELECT id, data, version
 FROM research_db_gridreference, jsonb_array_elements(data -> 'addresses') addresses
 WHERE (
   addresses ->> 'city' = city -- omit if city = null
   AND
   addresses ->> 'state' = state -- omit if state = null
   AND
   addresses ->> 'country' = country -- omit if country = null
 )
$$ LANGUAGE sql STABLE;

至少應傳入一個變數，以避免返回所有行。

但我在這個兔子洞走得太遠之前問的主要原因是我確信我在以完全錯誤的方式思考這個問題。必須有一種更標準的方式來處理這個問題。

順便說一句，我無法解決的另一件事是如何避免對 SELECT 語句進行硬編碼SELECT id, data, version。SELECT * 失敗，因為有一個額外的value列可能來自jsonb_array_elements(data -> 'addresses') addresses. 我不需要結果中的這個最終值。

處理這種情況的通常方法是這樣的條件：

where (city is null or addresses ->> 'city' = city)
 and (state is null or addresses ->> 'state' = state)
 and (country is null or addresses ->> 'country' = country)

但是OR這樣的情況通常會導致查詢緩慢。

另一種選擇是使用@>運算符並完全擺脫取消嵌套：

SELECT gr.*
FROM research_db_gridreference gr
where gr.data -> 'addresses' @> jsonb_build_array(jsonb_strip_nulls(jsonb_build_object('city', city, 'state', state, 'country', country)));

上述假設data被定義為jsonb（它應該是）。如果是json你需要使用相應的json_xxx功能。

如何避免硬編碼 SELECT 語句 SELECT id, data, version

其實這是正確的做法。select *在生產程式碼中不受歡迎。

但是，如果您只從定義為返回類型的表中選擇列，則可以輕鬆繞過附加列：

SELECT research_db_gridreference.*
FROM research_db_gridreference 
...

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

相關問答

Postgresql

如何將目前行傳遞給 Postgres 中的 Generated Column 函式？

  • July 31, 2022
檢視問答
Postgresql

NVL 的 Postgres 函式

  • February 10, 2022
檢視問答
Postgresql

列名作為觸發器中呼叫的函式的參數（PostgreSQL）

  • October 28, 2021
檢視問答
Postgresql

如何在 PostgreSQL 中使用數組作為 VARIADIC 函式的參數？

  • September 10, 2021
檢視問答
Postgresql

如何從 JSONB 中選擇具有給定鍵的子對象？

  • April 21, 2021
檢視問答
Postgresql

將變數作為輸入參數傳遞給 PostgreSQL 中另一個函式內的函式

  • January 31, 2021
檢視問答