Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
CREATE OR REPLACE FUNCTION public._compute_container_sort_key (
in_container_id uuid
)
RETURNS text AS
$body$
WITH container_default_sort_key AS (
SELECT * FROM (VALUES
('folder', 'index_asc'),
('album', 'index_asc'),
('blog', 'updated_desc')
) AS kv (container_type, default_sort_key)
)
SELECT COALESCE(c.sort_key, cdsk.default_sort_key) AS container_sort_key
FROM container c JOIN container_default_sort_key cdsk USING(container_type)
WHERE c.container_id = in_container_id;
$body$
LANGUAGE 'sql'
STABLE
RETURNS NULL ON NULL INPUT
SECURITY INVOKER
COST 8;
CREATE OR REPLACE FUNCTION public.list_container_by_parent_new (
in_parent_id uuid,
in_locale text,
in_sort_key text,
in_custom_init_key text
)
RETURNS SETOF public.node AS
$body$
-- 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 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 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,
-- Add a sort number based on the specified or calculated sort key
CASE COALESCE(in_sort_key, _compute_container_sort_key(ct.parent_container_id))
WHEN 'index_asc' THEN row_number() OVER ( ORDER BY c.sort_number ASC NULLS LAST, collkey(c.title, in_locale) ASC )
WHEN 'index_desc' THEN row_number() OVER ( ORDER BY c.sort_number DESC NULLS FIRST, collkey(c.title, in_locale) DESC )
WHEN 'size_asc' THEN row_number() OVER ( ORDER BY c.sort_number ASC NULLS LAST, collkey(c.title, in_locale) ASC )
WHEN 'size_desc' THEN row_number() OVER ( ORDER BY c.sort_number DESC NULLS FIRST, collkey(c.title, in_locale) DESC )
WHEN 'title_asc' THEN row_number() OVER ( ORDER BY collkey(c.title, in_locale) ASC )
WHEN 'title_desc' THEN row_number() OVER ( ORDER BY collkey(c.title, in_locale) DESC )
WHEN 'updated_asc' THEN row_number() OVER ( ORDER BY c.updated ASC NULLS FIRST, collkey(c.title, in_locale) ASC )
WHEN 'updated_desc' THEN row_number() OVER ( ORDER BY c.updated DESC NULLS LAST, collkey(c.title, in_locale) ASC )
WHEN 'created_asc' THEN row_number() OVER ( ORDER BY c.created ASC NULLS FIRST, collkey(c.title, in_locale) ASC )
WHEN 'created_desc' THEN row_number() OVER ( ORDER BY c.created DESC NULLS LAST, collkey(c.title, in_locale) ASC )
END AS _sort_number
FROM container_tree ct
JOIN container c ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id = in_parent_id
)
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
ORDER BY cc._sort_number ASC
$body$
LANGUAGE 'sql'
STABLE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 12 ROWS 50;
WITH child_containers AS (
SELECT
ct.child_container_id AS container_id,
CASE
WHEN 'view' IS NOT NULL THEN extract_custom_init_value(c.custom_init, 'view')
ELSE NULL
END AS _extracted_custom_init_value
FROM (
SELECT COALESCE( NULL, _compute_container_sort_key('dec521e1-432e-4304-8ba8-6cf59590d99b') )
) AS c_sort(key),
container_tree ct
JOIN container c ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b'
ORDER BY
CASE COALESCE(NULL, c_sort.key)
WHEN 'index_asc' THEN c.sort_number
WHEN 'size_asc' THEN c.sort_number
END ASC NULLS LAST,
CASE COALESCE(NULL, c_sort.key)
WHEN 'updated_asc' THEN c.updated
WHEN 'created_asc' THEN c.created
END ASC NULLS LAST,
CASE COALESCE(NULL, c_sort.key)
WHEN 'index_desc' THEN c.sort_number
WHEN 'size_desc' THEN c.sort_number
END DESC NULLS FIRST,
CASE COALESCE(NULL, c_sort.key)
WHEN 'updated_desc' THEN c.updated
WHEN 'created_desc' THEN c.created
END DESC NULLS FIRST,
CASE WHEN COALESCE(NULL, c_sort.key) LIKE '%\_asc'
THEN collkey(c.title, 'nb_NO')
END ASC,
CASE WHEN COALESCE(NULL, c_sort.key) LIKE '%\_desc'
THEN collkey(c.title, 'nb_NO')
END DESC
)
SELECT
cc.container_id,
'container'::text,
row_number() OVER ()::integer,
NULL::integer
FROM child_containers cc
WHERE cc._extracted_custom_init_value IS NULL
WITH
parent_container AS (
SELECT
c.container_id,
COALESCE( NULL, _compute_container_sort_key(c.container_id) ) AS parent_sort_key
FROM container c
WHERE c.container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b'
),
child_containers AS (
SELECT
ct.child_container_id AS container_id,
CASE
WHEN NULL IS NOT NULL THEN extract_custom_init_value(c.custom_init, NULL)
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
ORDER BY
CASE pc.parent_sort_key
WHEN 'index_asc' THEN c.sort_number
WHEN 'size_asc' THEN c.sort_number
END ASC NULLS LAST,
CASE pc.parent_sort_key
WHEN 'updated_asc' THEN c.updated
WHEN 'created_asc' THEN c.created
END ASC NULLS LAST,
CASE pc.parent_sort_key
WHEN 'index_desc' THEN c.sort_number
WHEN 'size_desc' THEN c.sort_number
END DESC NULLS FIRST,
CASE pc.parent_sort_key
WHEN 'updated_desc' THEN c.updated
WHEN 'created_desc' THEN c.created
END DESC NULLS FIRST,
CASE WHEN pc.parent_sort_key LIKE '%\_asc'
THEN collkey(c.title, 'nb_NO')
END ASC,
CASE WHEN pc.parent_sort_key LIKE '%\_desc'
THEN collkey(c.title, 'nb_NO')
END DESC
)
SELECT
cc.container_id,
'container'::text,
row_number() OVER ()::integer,
NULL::integer
FROM child_containers cc
WHERE cc._extracted_custom_init_value IS NULL
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.