PL/PGSQL function to convert text Grid Reference into OSGB Geometry

function_get_geom_from_gridref.sql

-- convert a grid-reference E.g. SH123456 into a northing easting geometry for postgis with SRID=27700 (OSGB36)
-- standing on the shoulders of...
-- http://www.movable-type.co.uk/scripts/latlong-os-gridref.html
-- https://github.com/chrisveness/geodesy/blob/master/osgridref.js [MIT]
-- consider this MIT licensed also.
CREATE OR REPLACE FUNCTION get_geom_from_grid_ref(IN grid_ref character varying)
    RETURNS public.geometry
    LANGUAGE 'plpgsql'
    
AS $BODY$
DECLARE
    parts text[];
    l1 integer; l2 integer;
    e100km integer; n100km integer;
    easting varchar; northing varchar;
BEGIN
	parts := regexp_matches(grid_ref,  '^([S,N,H,J,O,T])([A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]) ?([0-9]{5}) ?([0-9]{5})$');
    if parts is null or array_length(parts, 1) = 0 then
		parts := regexp_matches(grid_ref,  '^([S,N,H,J,O,T])([A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]) ?([0-9]{4}) ?([0-9]{4})$');
    end if;
	 
    if parts is null or array_length(parts, 1) = 0 then
        parts := regexp_matches(grid_ref,  '^([S,N,H,J,O,T])([A,B,C,D,E,F,G,H,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z]) ?([0-9]{3}) ?([0-9]{3})$');
    end if;
	 
     -- abandon.. not a grid ref
     if parts is null or array_length(parts, 1) = 0 then
        RAISE NOTICE 'Invalid grid reference: %', grid_ref;
        return null; 
     end if;

--    // get numeric values of letter references, mapping A->0, B->1, C->2, etc:
--    var l1 = gridref.toUpperCase().charCodeAt(0) - 'A'.charCodeAt(0);
--    var l2 = gridref.toUpperCase().charCodeAt(1) - 'A'.charCodeAt(0);
--    // shuffle down letters after 'I' since 'I' is not used in grid:
--    if (l1 > 7) l1--;
--    if (l2 > 7) l2--;
	  l1 := ascii(parts[1]) - ascii('A');
      l2 := ascii(parts[2]) - ascii('A');
      IF l1 > 7 THEN l1 := l1 - 1; END IF;
      IF l2 > 7 THEN l2 := l2 - 1; END IF;
   
--    // convert grid letters into 100km-square indexes from false origin (grid square SV):
--    var e100km = ((l1-2)%5)*5 + (l2%5);
--    var n100km = (19-Math.floor(l1/5)*5) - Math.floor(l2/5);

	  e100km := ((l1-2)%5)*5 + (l2%5);
      n100km := (19-floor(l1/5)*5) - floor(l2/5);
      
      IF (e100km<0 or e100km>6 or n100km<0 or n100km>12) THEN
      	RAISE EXCEPTION 'Invalid grid reference: %', grid_ref;
      END IF;
      
      easting  := e100km::varchar || rpad(parts[3], 5, '0');
      northing := n100km::varchar || rpad(parts[4], 5, '0');
      
      
      
return public.ST_GeomFROMEWKT('SRID=27700;POINT(' || easting || ' ' || northing ||  ')');
END
$BODY$;

function_get_gridref_from_geom.sql

-- convert SRID:27700-based OSGB36 coordinate point geometry to a OS Grid Reference  E.g. SH123456
-- standing on the shoulders of...
-- http://www.movable-type.co.uk/scripts/latlong-os-gridref.html
-- https://github.com/chrisveness/geodesy/blob/master/osgridref.js [MIT]
-- consider this MIT licensed also.
CREATE OR REPLACE FUNCTION get_grid_ref_from_geom(geom public.geometry)
 RETURNS character varying
 LANGUAGE plpgsql
AS $function$
        DECLARE
            e integer;     n integer;
            e100k integer; n100k integer;
            l1 integer;    l2 integer;
            letterPair varchar;
            -- needed to output left padded zeros
            e_char varchar;     n_char varchar;
        begin

                e := ST_X(geom)::integer;
                n := ST_Y(geom)::integer;

        -- // get the 100km-grid indices
                e100k := floor(e/100000);
                n100k := floor(n/100000);

        --      // translate those into numeric equivalents of the grid letters
        --    var l1 = (19-n100k) - (19-n100k)%5 + Math.floor((e100k+10)/5);
        --    var l2 = (19-n100k)*5%25 + e100k%5;

                l1 := (19-n100k) - (19-n100k)%5 + floor((e100k+10)/5);
                l2 := (19-n100k)*5%25 + e100k%5;

        --    // compensate for skipped 'I' and build grid letter-pairs
        --    if (l1 > 7) l1++;
        --    if (l2 > 7) l2++;

                IF l1 > 7 THEN l1 := l1 + 1; END IF;
                IF l2 > 7 THEN l2 := l2 + 1; END IF;

        --  var letterPair = String.fromCharCode(l1+'A'.charCodeAt(0), l2+'A'.charCodeAt(0));
                letterPair := chr( ascii('A') + l1  ) || chr( ascii('A') + l2  );

        --    // strip 100km-grid indices from easting & northing, and reduce precision
        --    e = Math.floor((e%100000)/Math.pow(10, 5-digits/2));
        --    n = Math.floor((n%100000)/Math.pow(10, 5-digits/2));
                e := floor(e%100000);
                n := floor(n%100000);


        --        // pad eastings & northings with leading zeros (just in case, allow up to 16-digit (mm) refs)
        --    e = ('00000000'+e).slice(-digits/2);
        --    n = ('00000000'+n).slice(-digits/2);
        --    return letterPair + ' ' + e + ' ' + n;


       -- the padding is pretty important!
       -- e.g 346728, 800834 becomes NJ46728834 which should be NJ4672800834
       -- and 346997, 804999 becomes NJ469974999 which should be NJ4699704999
                e_char := lpad(e::text, 5 ,'0');
                n_char := lpad(n::text, 5 ,'0');

                return letterPair || e_char || n_char;

        END
$function$;

Great reference, thanks. You could replace ST_GeomFROMEWKT with ST_MakePoint for better performance (no need to go through text representation, is there?)

I guess you could, but you would also need to set SRID too, along with converting the padded easting northings back into INT.

Not sure how different the performance would be, let me know if you run any tests :)