_compute_container_sort_key.sql

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;

list_container_by_parent_new.sql

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;

list_container_by_parent_using_order_by_case.sql

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

list_container_by_parent_using_order_by_case_and_cte_for_parent.sql

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