PostgreSQL functions for pluralizing strings and summarizing / humanizing time intervals.

pluralize_and_summarize_interval.sql

-- > select pluralize(42, 'friend');
-- '42 friends'
-- > select pluralize(1, 'ox', 'oxen');
-- '1 ox'
-- > select pluralize(32, 'is %s thing', 'are %s things')
-- 'are 32 things'
-- > select summarize_interval('interval 12.9 seconds')
-- '12 seconds'
-- > select summarize_interval('interval 3 hours 53 minutes')
-- '3 hours'

create or replace function pluralize(val numeric, singular text, plural text) returns text as $$
	select
		format((case when position('%' in format_str) > 0 then format_str else '%s ' || format_str end), val)
	from (
		select case
			when val = 1 then singular
			else plural
		end as format_str
	) as x
$$ language sql returns null on null input;

create or replace function pluralize(val double precision, singular text, plural text) returns text as $$
	select
		format((case when position('%' in format_str) > 0 then format_str else '%s ' || format_str end), val)
	from (
		select case
			when val = 1 then singular
			else plural
		end as format_str
	) as x
$$ language sql returns null on null input;

create or replace function pluralize(val numeric, singular text) returns text as $$
	select pluralize(val, singular, singular || 's')
$$ language sql returns null on null input;

create or replace function pluralize(val double precision, singular text) returns text as $$
	select pluralize(val, singular, singular || 's')
$$ language sql returns null on null input;

create or replace function summarize_interval(i_raw interval) returns text as $$
	select sign || case
		when i < interval '60 seconds' then pluralize(extract('seconds' from i)::integer, 'second'::text)
		when i < interval '60 minutes' then pluralize(extract('minutes' from i), 'minute'::text)
		when i < interval '24 hours' then pluralize(extract('hours' from i), 'hour'::text)
		else pluralize(extract('days' from i), 'day'::text)
	end
	from (
		select
			(case when i_raw < '0 seconds'::interval then -1 * i_raw else i_raw end) as i,
			(case when i_raw < '0 seconds'::interval then '-' else '' end) as sign
	) as x
$$ language sql returns null on null input;