list_container_by_parent.sql

SELECT
 c.container_id AS id
FROM container c
 JOIN container_tree ct ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id = 'dec521e1-432e-4304-8ba8-6cf59590d99b'
ORDER BY
CASE COALESCE( 'index_asc', _compute_container_sort_key('dec521e1-432e-4304-8ba8-6cf59590d99b') )
WHEN 'index_asc'    THEN c.sort_number ASC  NULLS LAST,  collkey(c.title, 'nb_NO') ASC
WHEN 'index_desc'   THEN c.sort_number DESC NULLS FIRST, collkey(c.title, 'nb_NO') DESC
WHEN 'size_asc'     THEN c.sort_number ASC  NULLS LAST,  collkey(c.title, 'nb_NO') ASC  -- same as index_asc
WHEN 'size_desc'    THEN c.sort_number DESC NULLS FIRST, collkey(c.title, 'nb_NO') DESC -- same as index_desc
WHEN 'title_asc'    THEN collkey(c.title, 'nb_NO') ASC
WHEN 'title_desc'   THEN collkey(c.title, 'nb_NO') DESC
WHEN 'updated_asc'  THEN c.updated ASC NULLS FIRST, collkey(c.title, 'nb_NO') ASC
WHEN 'updated_desc' THEN c.updated DESC NULLS LAST, collkey(c.title, 'nb_NO') ASC
WHEN 'created_asc'  THEN c.created ASC NULLS FIRST, collkey(c.title, 'nb_NO') ASC
WHEN 'created_desc' THEN c.created DESC NULLS LAST, collkey(c.title, 'nb_NO') ASC
END

plperl_list_container_by_parent.sql

CREATE OR REPLACE FUNCTION public.list_container_by_parent (
  parent_id uuid,
  locale text,
  sort_key text,
  skip_custom_init text
)
RETURNS SETOF public.node AS
$body$
my ($id, $locale, $sort_key, $skip_custom_init) = @_;

# If sort_key specified, just use it
unless ($sort_key) {
    # If parent specified, get sort_key for it, else get default
    my $rv=spi_exec_query(
      "SELECT container_sort_key("
      . ( $id ? "'$id'" : "null" )
      . ") AS sort_key"
    ,1);
    $sort_key=$rv->{'rows'}[0]->{'sort_key'};
}

# index_asc (default)
my $order_by=<<"EOM";
ORDER BY c.sort_number ASC NULLS LAST, collkey(c.title, '$locale') ASC
EOM

# index_desc
$order_by=<<"EOM" if $sort_key eq 'index_desc';
ORDER BY c.sort_number DESC NULLS FIRST, collkey(c.title, '$locale') DESC
EOM

# title_asc
$order_by=<<"EOM" if $sort_key eq 'title_asc';
ORDER BY collkey(c.title, '$locale') ASC
EOM

# title_desc
$order_by=<<"EOM" if $sort_key eq 'title_desc';
ORDER BY collkey(c.title, '$locale') DESC
EOM

# updated_asc
$order_by=<<"EOM" if $sort_key eq 'updated_asc';
ORDER BY c.updated ASC NULLS FIRST, collkey(c.title, '$locale') ASC
EOM

# updated_desc
$order_by=<<"EOM" if $sort_key eq 'updated_desc';
ORDER BY c.updated DESC NULLS LAST, collkey(c.title, '$locale') ASC
EOM

# created_asc
$order_by=<<"EOM" if $sort_key eq 'created_asc';
ORDER BY c.created ASC NULLS FIRST, collkey(c.title, '$locale') ASC
EOM

# created_desc
$order_by=<<"EOM" if $sort_key eq 'created_desc';
ORDER BY c.created DESC NULLS LAST, collkey(c.title, '$locale') ASC
EOM

# Handle skipping by custom_init field
my $skip_column;
if ( $skip_custom_init ) {
    my $skip_field = quote_literal($skip_custom_init);
    $skip_column = "extract_custom_init_value(c.custom_init, $skip_field)";
}
else {
    $skip_column = "NULL";
}

# Fetch and return child containers
my $sth;
if ( $id ) {
    my $id_literal = quote_literal($id);
    $sth = spi_query(<<"EOM");
SELECT c.container_id AS id, $skip_column AS skip
FROM container c JOIN container_tree ct ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id = $id_literal
$order_by
EOM
}
else {
    $sth = spi_query(<<"EOM");
SELECT c.container_id AS id, $skip_column AS skip
FROM container c LEFT JOIN container_tree ct ON ct.child_container_id = c.container_id
WHERE ct.parent_container_id IS NULL
$order_by
EOM
}

my $counter = 0;
while ( defined( $row = spi_fetchrow($sth) ) ) {
    next if $row->{'skip'};
    return_next({
        node_id     => $row->{'id'},
        node_type   => 'container',
        node_number => $counter,
        node_level  => undef,
    });
    $counter++;
}

return;
$body$
LANGUAGE 'plperl'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100 ROWS 1000;