CREATE OR REPLACE FUNCTION _upgis_extension( p_end_pt_id integer,
                       p_internal_pt_id integer,
                       p_extension      numeric, p_tolerance numeric, v_geom geometry)
RETURNS geometry
 AS
 $$
 DECLARE
    v_extend         numeric := p_extension;
    v_end_pt         geometry;
    v_internal_pt    geometry;
    v_deltaX        numeric;
    v_deltaY         numeric;
    v_length         numeric;
    v_new_point      geometry;
	v_x_round_factor integer;
	v_y_round_factor integer;
    new_geom geometry;
BEGIN
	IF SIGN(p_end_pt_id) > 0 THEN
		v_end_pt      := ST_PointN(v_geom,p_end_pt_id );
	ELSE
		v_end_pt      := ST_PointN(v_geom,ST_NumPoints(v_geom) + p_end_pt_id + 1 );
	END IF;

	IF SIGN(p_internal_pt_id) > 0 THEN
		v_internal_pt    := ST_PointN(v_geom,p_internal_pt_id );
	ELSE
		v_internal_pt    := ST_PointN(v_geom,ST_NumPoints(v_geom) + p_internal_pt_id  + 1);
	END IF;
     v_deltaX      := ST_X(v_end_pt) - ST_X(v_internal_pt);
     v_deltaY      := ST_Y(v_end_pt) - ST_Y(v_internal_pt);
     v_length      := ST_Distance( v_end_pt, v_internal_pt);
	 v_x_round_factor := round(log(10,(1/p_tolerance)/2));
	 v_y_round_factor := round(log(10,(1/p_tolerance)/2));
	 v_new_point := ST_Point(ROUND(CAST(ST_X(v_internal_pt) + v_deltaX * ( (v_Length + p_extension) / v_Length ) As numeric), v_x_round_factor),
	 					 ROUND(CAST(ST_Y(v_internal_pt) + v_deltaY * ( (v_Length + p_extension) / v_Length ) As numeric), v_y_round_factor)
						 );
	
	RAISE NOTICE '%', ST_AsText(v_new_point);
    /** new_geom  := ST_SetPoint(v_geom, CASE SIGN(p_end_pt_id) WHEN -1 THEN 0 ELSE 1 END,
                                            v_new_point
                                              );  **/

        IF SIGN(p_end_pt_id) = -1 THEN
	 	new_geom  := ST_AddPoint(v_geom,v_new_point, -1);
	ELSE 
		new_geom := ST_AddPoint(v_geom,v_new_point, 0); 
	END IF;

	RETURN new_geom;
END;
  $$
 LANGUAGE 'plpgsql' IMMUTABLE; 

CREATE OR REPLACE FUNCTION upgis_extend( p_geom  geometry, 
                 p_extension numeric, 
                 p_tolerance numeric,
                 p_end       in varchar default 'START' ) 
  RETURNS geometry
AS
$$
DECLARE 
  v_geom           geometry := p_geom;
  v_geom_length    numeric := 0;
  v_x_round_factor integer;
  v_y_round_factor integer;
  v_end            varchar(5) := UPPER(SUBSTR(p_end,1,5));
  INVALID_END      text := 'p_end value (' || v_end || ') must be START, BOTH or END';
  BAD_EXTENSION    text := 'Reduction of geometry of length (' || v_geom_length || ') of each end by (' || ABS(p_extension) || ') would result in zero length geometry.';
  NULL_EXTENSION   text := 'p_extension value must not be 0 or NULL.';
  NULL_TOLERANCE   text := 'p_tolerance may not be NULL.';
  NULL_GEOMETRY    text := 'p_geom may not be NULL.';
  NOT_LINEAR       text := 'p_geom must be a single linestring.';
BEGIN
  IF ( p_tolerance is null ) THEN
    RAISE EXCEPTION '%', NULL_TOLERANCE;
  END IF;
  v_x_round_factor := round(log(10,(1/p_tolerance)/2));
  v_y_round_factor := round(log(10,(1/p_tolerance)/2));
  IF ( v_geom is NULL ) THEN
    RAISE EXCEPTION '%', NULL_GEOMETRY;
  END IF;
  -- Only support simple linestrings
  IF ( NOT (GeometryType(v_geom) = 'LINESTRING') ) THEN 
    RAISE EXCEPTION '%', NOT_LINEAR;
  End If;
  IF ( NOT v_end IN ('START','BOTH','END') ) Then
    RAISE EXCEPTION '%', INVALID_END;
  END IF;
  IF ( p_extension is NULL OR p_extension = 0 ) THEN
    RAISE EXCEPTION '%', NULL_EXTENSION;
  END IF;
  IF ( SIGN(p_extension) = -1 ) THEN
    -- Is reduction distance (when BOTH) greater than actual length of string?
    v_geom_length := ST_Length(v_geom);
    IF ( ABS(p_extension) >= ( v_geom_length / CASE v_end WHEN 'BOTH' THEN 2.0 ELSE 1 END ) )  THEN
      RAISE EXCEPTION '%', BAD_EXTENSION;
    END IF;
  END IF;
  IF v_end IN ('START','BOTH') THEN 
    IF ( SIGN(p_extension) = 1 ) THEN
      v_geom := _upgis_extension(1,2,p_extension, p_tolerance, v_geom);
    ELSE
      --_upgis_reduction(1,2,p_extension);
    END IF;
  END IF;
  IF v_end IN ('BOTH','END') THEN
    IF ( SIGN(p_extension) = 1 ) THEN
      v_geom := _upgis_extension(-1,-2,p_extension, p_tolerance, v_geom);
    ELSE
      --v_geom := _upgis_reduction(-1,-2, p_extension, v_geom);
    END IF;
   END IF;
  RETURN v_geom;
END;
$$
LANGUAGE 'plpgsql' IMMUTABLE;

---Example use to extend
SELECT ST_AsText(upgis_extend(ST_GeomFromText('LINESTRING(1 1,2 2,3 2,4 3)'),1.414,0.05,'END')) ;
