Finding line segment of point on linestring using PostGIS

Here's a SQL function to compute the segment index of the LineString segment closest to a given point:

CREATE OR REPLACE FUNCTION ST_LineLocateN( line geometry, pt geometry )
RETURNS integer
AS $$
    SELECT i FROM (
    SELECT i, ST_Distance(
        ST_MakeLine( ST_PointN( line, s.i ), ST_PointN( line, s.i+1 ) ),
        pt) AS dist
      FROM generate_series(1, ST_NumPoints( line )-1) AS s(i)
      ORDER BY dist
    ) AS t LIMIT 1;
$$
LANGUAGE sql STABLE STRICT;

Example:

SELECT ST_LineLocateN( 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry, 
  'POINT(15 15.1)'::geometry);
==> 2

Here is SQL to find the index of the segment which is closest to a point:

WITH data(id, geom) AS (VALUES
    ( 1, 'LINESTRING (0 0, 10 10, 20 20, 30 30)'::geometry )
)
SELECT i FROM (
SELECT i, ST_Distance(
   ST_MakeLine( ST_PointN( data.geom, s.i ), ST_PointN( data.geom, s.i+1 ) ),
   'POINT(15 15.1)'::geometry ) AS dist
   FROM data JOIN LATERAL (
        SELECT i FROM generate_series(1, ST_NumPoints( data.geom )-1) AS gs(i)
     ) AS s(i) ON true
   ORDER BY dist
) AS t LIMIT 1;

Not the simplest, but it could be wrapped up in a function.

This probably has to be done by iterating over the line segments and finding the index of the one with minimum distance to the point.

This would be a nice addition to the ST_(3D)ClosestPoint functions. Or perhaps more appropriately it could be an extension to one or more of the Linear Referencing functions

If you want the segment index in order to add a vertex to the LineString at the closest point, then ST_Snap can be used to add the point in one step:

SELECT ST_AsText( ST_Snap('LINESTRING (0 0, 9 9)', 'POINT(1 1.1)', 0.2));