金额转换pgsql存储过程

CREATE OR REPLACE FUNCTION amount_to_words(amount NUMERIC, currency TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
units TEXT[] = ARRAY['zero', 'one', 'two', 'three', 'four', 'five', 'six', 'seven', 'eight', 'nine'];
teens TEXT[] = ARRAY['eleven', 'twelve', 'thirteen', 'fourteen', 'fifteen', 'sixteen', 'seventeen', 'eighteen', 'nineteen'];
tens TEXT[] = ARRAY['', 'ten', 'twenty', 'thirty', 'forty', 'fifty', 'sixty', 'seventy', 'eighty', 'ninety'];
thousands TEXT[] = ARRAY['', 'thousand', 'million', 'billion', 'trillion'];
words TEXT := '';
remaining_amount NUMERIC;
place INTEGER;
unit_value INTEGER;
tens_value INTEGER;
hundreds_value INTEGER;
BEGIN
IF amount = 0 THEN
RETURN units[1];
END IF;

FOR place IN 0..CEILING(LOG(10, amount)) LOOP
unit_value := floor(amount / (10 ^ place));
remaining_amount := amount % (10 ^ place);

IF unit_value > 0 THEN
hundreds_value := floor(unit_value / 100);
tens_value := floor((unit_value % 100) / 10);
IF hundreds_value > 0 THEN
words := units[hundreds_value] || ' hundred ' || words;
END IF;

IF tens_value > 1 THEN
words := tens[tens_value] || ' ' || words;
ELSIF tens_value = 1 THEN
words := teens[unit_value % 10] || ' ' || words;
END IF;

IF unit_value % 10 > 0 THEN
words := units[unit_value % 10] || ' ' || words;
END IF;

words := thousands[place + 1] || ' ' || words;
END IF;

amount := remaining_amount;
END LOOP;

words := regexp_replace(words, '\s+', ' ', 'g') || ' ' || currency;
RETURN words;
END;
$$;