Hello,
I'm in the very very very very early stages of migrating a MySQL/PHP app to PostgreSQL/PHP/Lua. Because we are moving to PostgreSQL one of the [many] things I intend to change is to move ALL the SQL code/logic out of the application layer and into the database where it belongs. So after months of reading the [fine] PostgreSQL manual my first experiment is to port some PHP/SQL code to a PostgreSQL function.
At this stage the function is a purely academic exercise because like I said before it's early days so no data has been migrated yet so I don't have data to test it against. My reason for sharing at such an early stage is because all I've done so far is read the [fine] manual and I'd like to know if I've groked at least some of the material.
I would appreciate any feedback you can provide. I am particularly interested in learning about the most efficient way to do things in PL/pgSQL because I would hate for the first iteration of the new version of the app to be slower than the old version.
Thank you for your consideration,
Dane
/**
* Returns the status of a coupon or voucher.
* @param _code The discount code.
* @return NULL if the discount does not exist otherwise a JSON object.
*
* Voucher codes have the following properties:
* type - The type of discount (voucher, giftcert).
*
* status - The status of the voucher. The valid values are:
* void - The voucher has been voided.
*
* expired - The voucher has expired.
*
* inactive - The gift certificate has not been sent yet.
*
* ok - The voucher has been activated, has not expired, and has a
* current value greater than zero.
*
* date - The expiration or activation or void date of the voucher in a reader
* friendly format.
*
* datetime - The expiration or activation or void date of the gift certificate in
* YYYY-MM-DD HH:MM:SS format.
*
* value - The current value of the voucher.
*
* The mandatory properties are type and status. The presence of the other properties
* are dependent on the value of status.
************************************************************************************
* Coupon codes can provide the following additional parameters that are used to
* determine if an order meets a coupon's minimum requirements.
* @param int seats The number of seats in the user's cart.
* @param numeric subtotal The order's subtotal.
*
* Coupon codes have the following properties:
* type - The type of discount (coupon).
*
* status - The status of the coupon code. The valid values are:
* void - The coupon has been voided.
*
* expired - The coupon has expired.
*
* inactive - The coupon has not been activated yet.
*
* min - The minimum seats or dollar amount requirement has not been
* met.
*
* ok - The coupon can be used.
*
* min - The minimum seats or dollar amount requirement. The value of this
* property is either an unsigned integer or dollar amount string w/ the
* dollar sign.
*
* date - The expiration or activation or void date of the coupon in a reader
* friendly format.
*
* datetime - The expiration or activation or void date of the coupon in YYYY-MM-DD
* HH:MM:SS format.
*
* value - The current value of the coupon as a string. The value of this property
* is either an unsigned integer w/ a percent symbol or dollar amount
* string w/ the dollar sign.
*/
CREATE OR REPLACE FUNCTION check_discount_code(
_code public.CITXT70,
VARIADIC cpnxtra NUMERIC[]
) RETURNS JSON AS $$
DECLARE
discount RECORD;
BEGIN
SELECT
ok,
v.value,
created,
expires,
modified,
effective_date,
-- The minimum quantity or dollar amount required to use the coupon.
COALESCE(
lower(qty_range),
'$' || to_char(lower(amount_range), '999999999999999D99')
) AS min,
CASE type::TEXT
WHEN 'voucher'
THEN
CASE WHEN gd.code IS NOT NULL THEN 'giftcert' END
ELSE type::TEXT
END AS type,
to_char(expires, 'Dy, MM Mon. YYYY') AS expd,
to_char(modified, 'Dy, MM Mon. YYYY') AS mdate,
to_char(effective_date, 'Dy, MM Mon. YYYY') AS edate,
-- Determines if the coupon has been used up.
CASE WHEN maxuse > 0 THEN maxuse - used <= 0 ELSE FALSE END AS maxuse,
effective_date > CURRENT_DATE AS notyet,
expires < CURRENT_DATE AS expired,
-- The coupon's discount value as a dollar amount or percent.
COALESCE(
discount_rate || '%',
'$' || to_char(discount_amount, '999999999999999D99')
) AS discount,
cpn.code IS NULL AS danglingcoupon,
v.code IS NULL AS danglingvoucher
INTO STRICT discount
FROM
discount_codes AS dc
LEFT JOIN coupons AS cpn USING (code)
LEFT JOIN vouchers AS v USING (code)
LEFT JOIN giftcerts_d AS gd USING (code)
WHERE
dc.code = _code;
IF FOUND THEN
CASE discount.type
WHEN 'coupon'
THEN
IF discount.danglingcoupon
THEN
-- This should NEVER happen!
DELETE FROM discount_codes WHERE code = _code;
RAISE WARNING 'Removed dangling coupon code: %', _code;
ELSE
IF discount.maxuse OR NOT discount.ok
THEN
RETURN json_build_object('status', 'void', 'type', 'coupon');
END IF;
IF discount.expired
THEN
RETURN json_build_object(
'type', 'coupon',
'status', 'expired',
'date', discount.expd,
'datetime', discount.expires
);
END IF;
IF discount.notyet THEN
RETURN json_build_object(
'type', 'coupon',
'date', discount.edate,
'status', 'inactive',
'datetime', discount.effective_date
);
END IF;
IF 2 = array_length(cpnxtra, 1)
THEN
IF discount.min IS NOT NULL
THEN
-- @TODO - Test the regex to ensure it is escaped properly.
IF discount.min ~ '^\$'
THEN
IF right(discount.min, -1)::NUMERIC > cpnxtra[1]::NUMERIC
THEN
RETURN json_build_object(
'status', 'min',
'type', 'coupon',
'min', discount.min
);
END IF;
ELSIF discount.min::INT > cpnxtra[0]::INT
THEN
RETURN json_build_object(
'status', 'min',
'type', 'coupon',
'min', discount.min
);
END IF;
RETURN json_build_object(
'status', 'ok',
'type', 'coupon',
'min', discount.min,
'value', discount.discount
);
END IF;
END IF;
RETURN json_build_object(
'status', 'ok',
'type', 'coupon',
'value', discount.discount
);
END IF;
ELSE
IF discount.danglingvoucher
THEN
-- This should NEVER happen!
DELETE FROM discount_codes WHERE code = _code;
RAISE WARNING 'Removed dangling voucher: %', _code;
ELSE
IF NOT discount.ok
THEN
RETURN json_build_object(
'status', 'void',
'type', discount.type,
'date', discount.mdate,
'datetime', discount.modified
);
END IF;
IF discount.expired
THEN
RETURN json_build_object(
'status', 'expired',
'type', discount.type,
'date', discount.expd,
'datetime', discount.expires
);
END IF;
IF discount.notyet
THEN
RETURN json_build_object(
'type', discount.type,
'date', discount.edate,
'status', 'inactive',
'datetime', discount.effective_date,
'value', to_char(discount.value, '999999999999999D99')
);
END IF;
IF discount.value > 0
THEN
RETURN json_build_object(
'status', 'ok',
'type', discount.type,
'date', discount.expd,
'datetime', discount.expires,
'value', to_char(discount.value, '999999999999999D99')
);
END IF;
RETURN json_build_object('status', 'depleted', 'type', discount.type);
END IF;
END CASE;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT;
* Returns the status of a coupon or voucher.
* @param _code The discount code.
* @return NULL if the discount does not exist otherwise a JSON object.
*
* Voucher codes have the following properties:
* type - The type of discount (voucher, giftcert).
*
* status - The status of the voucher. The valid values are:
* void - The voucher has been voided.
*
* expired - The voucher has expired.
*
* inactive - The gift certificate has not been sent yet.
*
* ok - The voucher has been activated, has not expired, and has a
* current value greater than zero.
*
* date - The expiration or activation or void date of the voucher in a reader
* friendly format.
*
* datetime - The expiration or activation or void date of the gift certificate in
* YYYY-MM-DD HH:MM:SS format.
*
* value - The current value of the voucher.
*
* The mandatory properties are type and status. The presence of the other properties
* are dependent on the value of status.
************************************************************************************
* Coupon codes can provide the following additional parameters that are used to
* determine if an order meets a coupon's minimum requirements.
* @param int seats The number of seats in the user's cart.
* @param numeric subtotal The order's subtotal.
*
* Coupon codes have the following properties:
* type - The type of discount (coupon).
*
* status - The status of the coupon code. The valid values are:
* void - The coupon has been voided.
*
* expired - The coupon has expired.
*
* inactive - The coupon has not been activated yet.
*
* min - The minimum seats or dollar amount requirement has not been
* met.
*
* ok - The coupon can be used.
*
* min - The minimum seats or dollar amount requirement. The value of this
* property is either an unsigned integer or dollar amount string w/ the
* dollar sign.
*
* date - The expiration or activation or void date of the coupon in a reader
* friendly format.
*
* datetime - The expiration or activation or void date of the coupon in YYYY-MM-DD
* HH:MM:SS format.
*
* value - The current value of the coupon as a string. The value of this property
* is either an unsigned integer w/ a percent symbol or dollar amount
* string w/ the dollar sign.
*/
CREATE OR REPLACE FUNCTION check_discount_code(
_code public.CITXT70,
VARIADIC cpnxtra NUMERIC[]
) RETURNS JSON AS $$
DECLARE
discount RECORD;
BEGIN
SELECT
ok,
v.value,
created,
expires,
modified,
effective_date,
-- The minimum quantity or dollar amount required to use the coupon.
COALESCE(
lower(qty_range),
'$' || to_char(lower(amount_range), '999999999999999D99')
) AS min,
CASE type::TEXT
WHEN 'voucher'
THEN
CASE WHEN gd.code IS NOT NULL THEN 'giftcert' END
ELSE type::TEXT
END AS type,
to_char(expires, 'Dy, MM Mon. YYYY') AS expd,
to_char(modified, 'Dy, MM Mon. YYYY') AS mdate,
to_char(effective_date, 'Dy, MM Mon. YYYY') AS edate,
-- Determines if the coupon has been used up.
CASE WHEN maxuse > 0 THEN maxuse - used <= 0 ELSE FALSE END AS maxuse,
effective_date > CURRENT_DATE AS notyet,
expires < CURRENT_DATE AS expired,
-- The coupon's discount value as a dollar amount or percent.
COALESCE(
discount_rate || '%',
'$' || to_char(discount_amount, '999999999999999D99')
) AS discount,
cpn.code IS NULL AS danglingcoupon,
v.code IS NULL AS danglingvoucher
INTO STRICT discount
FROM
discount_codes AS dc
LEFT JOIN coupons AS cpn USING (code)
LEFT JOIN vouchers AS v USING (code)
LEFT JOIN giftcerts_d AS gd USING (code)
WHERE
dc.code = _code;
IF FOUND THEN
CASE discount.type
WHEN 'coupon'
THEN
IF discount.danglingcoupon
THEN
-- This should NEVER happen!
DELETE FROM discount_codes WHERE code = _code;
RAISE WARNING 'Removed dangling coupon code: %', _code;
ELSE
IF discount.maxuse OR NOT discount.ok
THEN
RETURN json_build_object('status', 'void', 'type', 'coupon');
END IF;
IF discount.expired
THEN
RETURN json_build_object(
'type', 'coupon',
'status', 'expired',
'date', discount.expd,
'datetime', discount.expires
);
END IF;
IF discount.notyet THEN
RETURN json_build_object(
'type', 'coupon',
'date', discount.edate,
'status', 'inactive',
'datetime', discount.effective_date
);
END IF;
IF 2 = array_length(cpnxtra, 1)
THEN
IF discount.min IS NOT NULL
THEN
-- @TODO - Test the regex to ensure it is escaped properly.
IF discount.min ~ '^\$'
THEN
IF right(discount.min, -1)::NUMERIC > cpnxtra[1]::NUMERIC
THEN
RETURN json_build_object(
'status', 'min',
'type', 'coupon',
'min', discount.min
);
END IF;
ELSIF discount.min::INT > cpnxtra[0]::INT
THEN
RETURN json_build_object(
'status', 'min',
'type', 'coupon',
'min', discount.min
);
END IF;
RETURN json_build_object(
'status', 'ok',
'type', 'coupon',
'min', discount.min,
'value', discount.discount
);
END IF;
END IF;
RETURN json_build_object(
'status', 'ok',
'type', 'coupon',
'value', discount.discount
);
END IF;
ELSE
IF discount.danglingvoucher
THEN
-- This should NEVER happen!
DELETE FROM discount_codes WHERE code = _code;
RAISE WARNING 'Removed dangling voucher: %', _code;
ELSE
IF NOT discount.ok
THEN
RETURN json_build_object(
'status', 'void',
'type', discount.type,
'date', discount.mdate,
'datetime', discount.modified
);
END IF;
IF discount.expired
THEN
RETURN json_build_object(
'status', 'expired',
'type', discount.type,
'date', discount.expd,
'datetime', discount.expires
);
END IF;
IF discount.notyet
THEN
RETURN json_build_object(
'type', discount.type,
'date', discount.edate,
'status', 'inactive',
'datetime', discount.effective_date,
'value', to_char(discount.value, '999999999999999D99')
);
END IF;
IF discount.value > 0
THEN
RETURN json_build_object(
'status', 'ok',
'type', discount.type,
'date', discount.expd,
'datetime', discount.expires,
'value', to_char(discount.value, '999999999999999D99')
);
END IF;
RETURN json_build_object('status', 'depleted', 'type', discount.type);
END IF;
END CASE;
END IF;
RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT;
