path: root/sql/modules/Location.sql

-- VERSION 1.3.0
CREATE OR REPLACE FUNCTION location_save
(in_id int, in_companyname text, in_address1 text, in_address2 text, 
    in_city text, in_state text, in_zipcode text, in_country text) 
returns integer AS
$$
DECLARE
    location_id integer;
BEGIN
    UPDATE locations
    SET companyname = in_companyname,
        address1 = in_address1,
        address2 = in_address2,
        city = in_city,
        state = in_state,
        zipcode = in_zipcode,
        country = in_country
    WHERE id = in_id;
    IF FOUND THEN
        return in_id;
    END IF;
    INSERT INTO location 
    (companyname, address1, address2, city, state, zipcode, country)
    VALUES
    (in_companyname, in_address1, in_address2, in_city, in_state,
        in_zipcode, in_country);
    SELECT lastval('location_id_seq') INTO location_id;
    return location_id;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION location_get (in_id integer) returns locations AS
$$
DECLARE
    location locations%ROWTYPE;
BEGIN
    SELECT * INTO location FROM locations WHERE id = in_id;
    RETURN location;
END;
$$ language plpgsql;

CREATE OR REPLACE FUNCTION location_search 
(in_companyname varchar, in_address1 varchar, in_address2 varchar, 
    in_city varchar, in_state varchar, in_zipcode varchar, 
    in_country varchar)
RETURNS SETOF locations
AS
$$
DECLARE
    location locations%ROWTYPE;
BEGIN
    FOR location IN
        SELECT * FROM locations 
        WHERE companyname ilike '%' || in_companyname || '%'
            AND address1 ilike '%' || in_address1 || '%'
            AND address2 ilike '%' || in_address2 || '%'
            AND in_city ilike '%' || in_city || '%'
            AND in_state ilike '%' || in_state || '%'
            AND in_zipcode ilike '%' || in_zipcode || '%'
            AND in_country ilike '%' || in_country || '%'
    LOOP
        RETURN NEXT location;
    END LOOP;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION location_list_all () RETURNS SETOF locations AS
$$
DECLARE 
    location locations%ROWTYPE;
BEGIN
    FOR location IN
        SELECT * FROM locations 
        ORDER BY company_name, city, state, country
    LOOP
        RETURN NEXT location;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION location_delete (in_id integer) RETURNS VOID AS
$$
BEGIN
    DELETE FROM locations WHERE id = in_id;
END;
$$ language plpgsql;