Database Administrators Asked by Dean Kayton on October 28, 2021
I have a working function. But it only works if I literally supply both the city and country variables.
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;
This returns data such as:
| 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 |
I want to be able to do something like:
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;
At least one variable should be passed in so as to avoid returning all rows.
But the main reason I am asking before going too far down this rabbit hole is that I am sure I am thinking about this in completely the wrong way. There has to be a more standard way of handling this.
As an aside, another thing I can’t work out is how to avoid hardcoding the SELECT statement SELECT id, data, version. SELECT * fails due to an extra value column that presumably comes from jsonb_array_elements(data -> 'addresses') addresses. I don’t need this final value in the result.
The usual way to deal with that are conditions like this:
where (city is null or addresses ->> 'city' = city)
and (state is null or addresses ->> 'state' = state)
and (country is null or addresses ->> 'country' = country)
But OR conditions like that typically lead to slow queries.
Another option is to use the @> operator and gid rid of the unnesting completely:
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)));
The above assumes data is defined as jsonb (which it should be). If it's json you need to use the corresponding json_xxx functions.
how to avoid hardcoding the SELECT statement SELECT id, data, version
Actually that's the correct thing to do. select * is frowned upon in production code.
But you can easily get around the additional column if you only select columns from the table defined as the return type:
SELECT research_db_gridreference.*
FROM research_db_gridreference
...
Answered by a_horse_with_no_name on October 28, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP