31 lines
1019 B
PL/PgSQL
31 lines
1019 B
PL/PgSQL
alter table storage.objects add column if not exists path_tokens text[] generated always as (string_to_array("name", '/')) stored;
|
|
|
|
CREATE OR REPLACE FUNCTION storage.search(prefix text, bucketname text, limits int DEFAULT 100, levels int DEFAULT 1, offsets int DEFAULT 0)
|
|
RETURNS TABLE (
|
|
name text,
|
|
id uuid,
|
|
updated_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ,
|
|
last_accessed_at TIMESTAMPTZ,
|
|
metadata jsonb
|
|
)
|
|
LANGUAGE plpgsql
|
|
AS $function$
|
|
BEGIN
|
|
return query
|
|
with files_folders as (
|
|
select path_tokens[levels] as folder
|
|
from storage.objects
|
|
where objects.name ilike prefix || '%'
|
|
and bucket_id = bucketname
|
|
GROUP by folder
|
|
limit limits
|
|
offset offsets
|
|
)
|
|
select files_folders.folder as name, objects.id, objects.updated_at, objects.created_at, objects.last_accessed_at, objects.metadata from files_folders
|
|
left join storage.objects
|
|
on prefix || files_folders.folder = objects.name
|
|
where objects.id is null or objects.bucket_id=bucketname;
|
|
END
|
|
$function$;
|