Postgres DBlink Example

dblink is one of the features available with postgres to do something by connecting remote pgdatabase from pg-sql.

To enable dblink execute the file located @ \share\contrib with the name of dblink.sql in to the required DB.

Following example function is to extract remote db's data and migrate it to local. Structure of the both tables vary is the notable one.

-- Function: extractobudata()

-- DROP FUNCTION extractobudata();

CREATE OR REPLACE FUNCTION extractobudata()
  RETURNS void AS
$BODY$
DECLARE
    last_extracted_data numeric;
BEGIN
    select max(packetid) into last_extracted_data from obuperiodicdata;

    INSERT INTO
        obuperiodicdata
        (packetid,
        obuserialnumber,
        companysign,
        gpsspeed,
        lat,
        lon,
        alt,
        heading,
        storedon,
        distance,
        enginerpm,
        receivedon,
        poiid)
                SELECT *
        FROM dblink('dbname= port=5432 host= user= password=', 'select
        af_sno,
  af_obu_serial_no ,
af_company_sign,
  af_gps_speed,
  af_lat,
  af_lon,
  af_alt,
  af_heading,
  af_timestamp,
  af_distance,
  af_engine_rpm,
  af_received_time,
  poi_id
        from at_obu_periodic_data where af_sno > '|| last_extracted_data  )
        AS t1(packetid numeric ,
        obuserialnumber character(100),
        companysign character(100) ,
        gpsspeed numeric,
        lat numeric,
        lon numeric,
        alt numeric,
        heading numeric,
        storedon timestamp,
        distance numeric,
        enginerpm numeric,
        receivedon timestamp,
        poiid integer );
   
END
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION extractobudata() OWNER TO postgres;