Search Postgresql Archives

My first PL/pgSQL function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux