Skip to content

Instantly share code, notes, and snippets.

@mhouchin
Created April 22, 2016 07:51
Show Gist options
  • Save mhouchin/7ba06d315009d9d05d7d88a17d8266b0 to your computer and use it in GitHub Desktop.
Save mhouchin/7ba06d315009d9d05d7d88a17d8266b0 to your computer and use it in GitHub Desktop.
PostgreSQL function to generate sonata media path
-- Function: public.sonata_media_path(bigint, text, text, text)
-- DROP FUNCTION public.sonata_media_path(bigint, text, text, text);
CREATE OR REPLACE FUNCTION public.sonata_media_path(
id bigint,
context text,
size text,
reference text)
RETURNS text AS
$BODY$
DECLARE
firstLevel bigint;
secondLevel bigint;
repFirstLevel text;
repSecondLevel text;
filepath text;
filename text;
BEGIN
-- initial calculations and 0 padding
firstLevel := (id / 100000);
secondLevel := ((id - (firstLevel * 100000)) / 1000);
-- 0 padding
repFirstLevel := lpad((firstLevel + 1)::text, 4, '0');
repSecondLevel := lpad((secondLevel + 1)::text, 2, '0');
-- make the filepath
filepath := '/uploads/media/' || context || '/' || repFirstLevel || '/' || repSecondLevel || '/';
-- make the filename
filename := 'thumb_' || id::text || '_' || context || '_' || size || '.' || split_part(reference, '.', 2);
RETURN filepath || filename;
END;
$BODY$
LANGUAGE plpgsql STABLE SECURITY DEFINER
COST 100;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment