Last active
March 20, 2019 09:48
-
-
Save robinsmidsrod/f0814f3e0d229dfbf0db81a5861ba935 to your computer and use it in GitHub Desktop.
Trying to get away from extension https://pgxn.org/dist/pg_collkey/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE FUNCTION public.list_container_by_parent(in_parent_id uuid, in_locale text, in_sort_key text, in_custom_init_key text) RETURNS SETOF public.node | |
LANGUAGE sql STABLE COST 30 ROWS 20 | |
AS $$ | |
-- Returns the child containers of the specified parent container in the | |
-- sort order specified by the sort key and locale. | |
-- If sort key is not specified, the sort key of the parent container, | |
-- or default for parent container type, is used. | |
-- If custom_init key is specified, containers that have this key set are | |
-- excluded from output. The value of the custom_init key doesn't matter. | |
WITH | |
container_default_sort_key(container_type, default_sort_key) AS ( | |
VALUES | |
('folder', 'index_asc'), | |
('album', 'index_asc'), | |
('blog', 'updated_desc') | |
), | |
parent_container AS ( | |
SELECT | |
c.container_id, | |
COALESCE( | |
in_sort_key, | |
c.sort_key, | |
cdsk.default_sort_key | |
) AS parent_sort_key | |
FROM container c | |
JOIN container_default_sort_key cdsk USING(container_type) | |
WHERE c.container_id = in_parent_id | |
), | |
child_containers AS ( | |
SELECT | |
ct.child_container_id AS container_id, | |
-- Only extract custom_init value if key was specified | |
CASE | |
WHEN in_custom_init_key IS NOT NULL | |
THEN extract_custom_init_value(c.custom_init, in_custom_init_key) | |
ELSE NULL | |
END AS _extracted_custom_init_value | |
FROM container_tree ct | |
JOIN container c ON ct.child_container_id = c.container_id | |
JOIN parent_container pc ON ct.parent_container_id = pc.container_id | |
-- Sort based on specified or calculated sort key | |
-- Using COALESCE(in_sort_key, ...) here ensures constant folding | |
-- of the entire sort expression | |
ORDER BY | |
CASE COALESCE(in_sort_key, pc.parent_sort_key) | |
WHEN 'index_asc' THEN c.sort_number | |
WHEN 'size_asc' THEN c.sort_number | |
END ASC NULLS LAST, | |
CASE COALESCE(in_sort_key, pc.parent_sort_key) | |
WHEN 'updated_asc' THEN c.updated | |
WHEN 'created_asc' THEN c.created | |
END ASC NULLS FIRST, | |
CASE COALESCE(in_sort_key, pc.parent_sort_key) | |
WHEN 'index_desc' THEN c.sort_number | |
WHEN 'size_desc' THEN c.sort_number | |
END DESC NULLS FIRST, | |
CASE COALESCE(in_sort_key, pc.parent_sort_key) | |
WHEN 'updated_desc' THEN c.updated | |
WHEN 'created_desc' THEN c.created | |
END DESC NULLS LAST, | |
CASE COALESCE(in_sort_key, pc.parent_sort_key) | |
WHEN 'index_asc' THEN collkey(c.title, in_locale) | |
WHEN 'title_asc' THEN collkey(c.title, in_locale) | |
WHEN 'size_asc' THEN collkey(c.title, in_locale) | |
WHEN 'updated_asc' THEN collkey(c.title, in_locale) | |
WHEN 'created_asc' THEN collkey(c.title, in_locale) | |
WHEN 'updated_desc' THEN collkey(c.title, in_locale) | |
WHEN 'created_desc' THEN collkey(c.title, in_locale) | |
END ASC, | |
CASE COALESCE(in_sort_key, pc.parent_sort_key) | |
WHEN 'index_desc' THEN collkey(c.title, in_locale) | |
WHEN 'title_desc' THEN collkey(c.title, in_locale) | |
WHEN 'size_desc' THEN collkey(c.title, in_locale) | |
END DESC | |
) | |
SELECT | |
cc.container_id, | |
'container'::text, | |
row_number() OVER ()::integer, | |
NULL::integer | |
FROM child_containers cc | |
-- Exclude containers where custom_init value is present | |
WHERE cc._extracted_custom_init_value IS NULL | |
$$; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE OR REPLACE FUNCTION public.mycollkey ( | |
value pg_catalog.anyelement, | |
locale text | |
) | |
RETURNS pg_catalog.anyelement AS | |
$body$ | |
BEGIN | |
EXECUTE 'SELECT $1 COLLATE ' || quote_ident(locale) INTO $0 USING value; | |
RETURN $0; | |
--EXCEPTION | |
-- WHEN OTHERS THEN | |
-- RETURN NULL; | |
END; | |
$body$ | |
LANGUAGE 'plpgsql' | |
IMMUTABLE | |
CALLED ON NULL INPUT | |
SECURITY INVOKER | |
COST 4; | |
SELECT | |
'b' < 'å' COLLATE "nb_NO" AS nb_NO_native, -- should return true | |
'b' < 'å' COLLATE "en_US" AS en_US_native, -- should return false | |
'b' < mycollkey('å'::text, 'nb_NO') AS nb_NO_mycollkey, -- should return true | |
'b' < mycollkey('å'::text, 'en_US') AS en_US_mycollkey -- should return false | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment