Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Trying to get away from extension https://pgxn.org/dist/pg_collkey/
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
$$;
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
You can’t perform that action at this time.