PostgreSQL: plpgsql: Example of using ARRAY data type (one and two-dimentional) in plsql function

Intro: PostgreSQL supports ARRAY using in plpgsql functions. This example shows how to define, use and iterate throw arrays in plpgsql procedures.

Environment:
- Win 7
- PostgreSQL 9.2.4

Preconditions:
- PostgreSQL installed with plpgsql support
- pgAdmin III installed

Steps:
1. Open pgAdmin III
2. Select PostgreSQL server (double click in object browser)

3. Select Database

4. Select Schema

5. Select "Functions"

6. Click "SQL" button to open Query Editor

7. Type plpgsql script code and execute it:

CREATE OR REPLACE FUNCTION using_array_example()
RETURNS character varying
AS $$
DECLARE

    --------------------------------------------------------------------------
    -- Description: This is example script using plpgsql ARRAY. It creates 4 tables(voyage,warehouse,driver,vehicle) and fill them with data.
    --------------------------------------------------------------------------

    drivers     character varying[] := ARRAY['Darth Vader', 'Obi Van Kenobi', 'Luke Skywalker'];
    truck_models    character varying[] := ARRAY['Volvo V400', 'Mercedes G550' , 'Scania F440'];
    warehouses character varying[][] := ARRAY[['USA','New York', ' Address 1'],['USA','Los Angeles','Address 2'],['USA','Princeton','Address 3'],['USA','Texas','Address 4']]; -- country, city, address

    rec RECORD;
    s character varying;
    arr character varying[];
   
BEGIN
    --------------------------------------------------------------------------
    -- DATA DEFINITION
    --------------------------------------------------------------------------
   
    -- Delete tables START
    DROP TABLE IF EXISTS voyage;
    DROP TABLE IF EXISTS warehouse;
    DROP TABLE IF EXISTS driver;
    DROP TABLE IF EXISTS vehicle;
    -- Delete tables END

    -- Delete SEQUENCE for table id START
    DROP SEQUENCE IF EXISTS voyage_id_seq;
    DROP SEQUENCE IF EXISTS vehicle_id_seq;
    DROP SEQUENCE IF EXISTS driver_id_seq;
    DROP SEQUENCE IF EXISTS warehouse_id_seq;
    -- Delete SEQUENCE for table id END

    -- Sequencers definition START
    CREATE SEQUENCE voyage_id_seq START 1000;
    CREATE SEQUENCE vehicle_id_seq START 1000;
    CREATE SEQUENCE driver_id_seq START 1000;
    CREATE SEQUENCE warehouse_id_seq START 1000;
    -- Sequencers definition END
   

    -- Tables defenition START
   
    -- Vehicle
    CREATE TABLE vehicle(
            vehicle_id         numeric(10) DEFAULT nextval('vehicle_id_seq') PRIMARY KEY
        ,    truck_model         character varying(255)
    );

    -- Driver
    CREATE TABLE driver(
            driver_id         numeric(10) DEFAULT nextval('driver_id_seq') PRIMARY KEY
        ,    full_name         character varying(255) UNIQUE
    );

    -- Warehouse
    CREATE TABLE warehouse(
            warehouse_id         numeric(10) DEFAULT nextval('warehouse_id_seq') PRIMARY KEY
        ,    country         character varying(255) NOT NULL
        ,    city             character varying(255) NOT NULL
        ,    address            character varying(255) NOT NULL
    );

    -- Voyage
    CREATE TABLE voyage(
   
            voyage_id         numeric(10) DEFAULT nextval('voyage_id_seq') PRIMARY KEY

        ,    driver_id         numeric(10) REFERENCES driver(driver_id)
        ,    vehicle_id         numeric(10) REFERENCES vehicle(vehicle_id)

        ,    from_warehouse_id    numeric(10) REFERENCES warehouse(warehouse_id)
        ,    to_warehouse_id        numeric(10) REFERENCES warehouse(warehouse_id)

        ,    created         timestamp without time zone DEFAULT now()

    );
    -- Tables defenitin END
    --------------------------------------------------------------------------------------
    -- DATA GENERATION
    --------------------------------------------------------------------------------------

    -- "driver" fill
    FOREACH s IN ARRAY drivers LOOP
        INSERT INTO driver(full_name) VALUES(s);
    END LOOP;
   
    -- "vehicle" fill
    FOREACH s IN ARRAY truck_models LOOP
        INSERT INTO vehicle(truck_model) VALUES(s);
    END LOOP;

    -- "warehouse" fill
    FOREACH arr SLICE 1 IN ARRAY warehouses LOOP
        INSERT INTO warehouse(country,city,address) VALUES(arr[1],arr[2],arr[3]);
    END LOOP;

    -- "voyage" fill

    FOR rec IN
        -- query to generate all possible combinations
        SELECT *
        FROM
            driver AS dr
        ,    vehicle AS v
        ,     warehouse AS from_w
        ,    warehouse AS to_w
    LOOP
        INSERT INTO voyage(
                driver_id
            ,    vehicle_id
            ,    from_warehouse_id
            ,    to_warehouse_id)
        VALUES(
                rec.driver_id
            ,    rec.vehicle_id
            ,    rec.warehouse_id
            ,    rec.warehouse_id
        );

    END LOOP;   
       
   

    RETURN 'Hooray. Tables created and filled with data!!!';

END;
$$ LANGUAGE plpgsql;

8. Execute function:

SELECT using_array_example();