collection_summaries.sql

collection_summaries.sql

/*
    This create function script must be run as a user that owns the collections table!
    By setting SECURITY DEFINER on the function, we are telling Postgres that this function will run
    with the permissions of the user that created the function rather than the user that is calling the function.

    Because this does add a bit of security risk, we take a bit of extra precaution and schema qualify
    any tables that we are referencing and set the SEARCH_PATH to only explicity include pg_catalog and pg_temp schemas.

    By running the function as the user that owns the collections table, we are able to enable and disable the triggers that are set on
    that table.
*/
CREATE OR REPLACE FUNCTION dashboard.update_default_summaries(_collection_id text) RETURNS VOID AS $$
    ALTER TABLE pgstac.collections DISABLE TRIGGER collections_trigger;
    ALTER TABLE pgstac.collections DISABLE TRIGGER queryables_collection_trigger;

    WITH coll_item_cte AS (
        SELECT jsonb_build_object(
            'summaries',
            jsonb_build_object(
                'datetime', (
                    CASE
                    WHEN (collections."content"->>'dashboard:is_periodic')::boolean
                    THEN (to_jsonb(array[
                        to_char(min(datetime) at time zone 'Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'),
                        to_char(max(datetime) at time zone 'Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"')]))
                    ELSE jsonb_agg(distinct to_char(datetime at time zone 'Z', 'YYYY-MM-DD"T"HH24:MI:SS"Z"'))
                    END
                ),
                'cog_default', (
                    CASE
                    WHEN collections."content"->'item_assets' ? 'cog_default'
                    THEN jsonb_build_object(
                        'min', min((items."content"->'assets'->'cog_default'->'raster:bands'-> 0 ->'statistics'->>'minimum')::float),
                        'max', max((items."content"->'assets'->'cog_default'->'raster:bands'-> 0 ->'statistics'->>'maximum')::float)
                        )
                    ELSE NULL
                    END
                )
            )
        ) summaries,
        collections.id coll_id
        FROM pgstac.items
        JOIN collections on items.collection = collections.id
        WHERE collections.id = _collection_id
        GROUP BY collections."content" , collections.id
    )
    UPDATE pgstac.collections SET "content" = "content" || coll_item_cte.summaries
    FROM coll_item_cte
    WHERE collections.id = coll_item_cte.coll_id;

    ALTER TABLE pgstac.collections ENABLE TRIGGER collections_trigger;
    ALTER TABLE pgstac.collections ENABLE TRIGGER queryables_collection_trigger;
$$ LANGUAGE SQL SET SEARCH_PATH TO pg_catalog, pg_temp SECURITY DEFINER;