PostgreSQL how to add ordinal numbers to rows created by regexp_split_to_table()?

Question

I have two tables. Table logfile contains whole text file in one attribute; table rawtext should contain one line of text from the text file in each row:

create table LOGFILE ( LOGF_PK serial primary key, TEXT_CONTENT text ) ;
create table LOGTEXT ( LT_PK serial primary key, 
  LOGF_PK integer not null references LOGFILE( LOGF_PK ),
  LINENUM  integer,
  LINETEXT varchar(255)
);

I can split TEXT_CONTENT into a set of rows, but I am unable (don't know how) to generate line numbers in table LOGTEXT, e.g. the first line from LOGFILE.TEXT_CONTENT would be in a row <LT_PK>, <LOGF_PK>, 1, 'the first line of text', etc.

This query returns what I expect to obtain, but without line numbers:

select LOGF_PK, regexp_split_to_table( TEXT_CONTENT, '\r?\n' ) as LINETEXT ;

However, I cannot obtain any line numbers. I tried variant 1:

select LOGF_PK
  , row_number() over (partition by LOGFILE.LOGF_PK) as LINENUM
  , regexp_split_to_table( TEXT_CONTENT, '\r?\n' )) as LINETEXT
from LOGFILE

In the above example, LINENUM is always 1...

And when I try another possibility, variant 2:

select LOGF_PK
  , regexp_split_to_table( TEXT_CONTENT, '\r?\n' )) with ORDINALITY
from LOGFILE

I get error

ERROR:  syntax error at or near "with"
LINE 3:   , regexp_split_to_array( content, '\r?\n' ) with ordinalit...
                                                      ^

Unfortunately, there are very few examples of WITH ORDINALITY in PostgreSQL documentation and the few that are there are very general and unexplaining. And I suspect that some examples from documentation have never worked on a real system.

Database is PostgreSQL 12.8

Answer 1

WITH ORDINALITY can only be used when the generating function is used in place of a table, a lateral subquery can allow to use it that way for example:

select
  LOGF_PK,
  LINENUM,
  LINETEXT
from LOGFILE,
lateral regexp_split_to_table( TEXT_CONTENT, '\r?\n' )
            WITH ORDINALITY as t(LINETEXT,LINENUM) ;

another possibility is to move the row_number() call into an outer query:

select *, row_number() over (partition by tmp.LOGF_PK) as LINENUM
from (select LOGF_PK
  , regexp_split_to_table( TEXT_CONTENT, '\r?\n' ) as LINETEXT 
  from LOGFILE
) tmp;

http://sqlfiddle.com/#!17/93ee4/18

Answer 2

Move the function to the FROM clause (where it belongs), then you can use with ordinality

select l.LOGF_PK,
       t.linetext,
       t.linenum
from LOGFILE l
  cross join regexp_split_to_table(l.TEXT_CONTENT, '\r?\n' ) with ordinality as t(linetext, linenum)

Answer 3

The problem is that you are using a rather confusing feature of PostgreSQL, which is to place a set-returning function in the SELECT, rather than in the FROM. The documentation on it is difficult to find, so it's not surprising most don't understand how it works.

What it will do is apply that set to each row of the resultset, which means that the row-numbering is calculated before the set is laterally applied to the original resultset.

Effectively, your code is transformed

select LOGF_PK
  , row_number() over (partition by LOGFILE.LOGF_PK) as LINENUM
  , regexp_split_to_table( TEXT_CONTENT, '\r?\n' )) as LINETEXT
from LOGFILE

to this

SELECT *
FROM (
    select LOGF_PK
      , row_number() over (partition by LOGFILE.LOGF_PK) as LINENUM
    FROM LOGFILE
) LOGFILE
LATERAL regexp_split_to_table( TEXT_CONTENT, '\r?\n' )) as LINETEXT

So it's quite obvious now why the row-number doesn't work.

Instead place the lateral join in the correct position in the FROM

select LOGF_PK
  , row_number() over (partition by LOGFILE.LOGF_PK) as LINENUM
  , LINETEXT.LINETEXT
from LOGFILE,
LATERAL regexp_split_to_table( TEXT_CONTENT, '\r?\n' )) as LINETEXT

If you want to ensure the row-numbering is in order of the results from regexp_split_to_table, that is when you use WITH ORDINALITY, which returns an extra column to sort by

select LOGF_PK
  , row_number() over (partition by LOGFILE.LOGF_PK ORDER BY LINETEXT.ordinality) as LINENUM
  , LINETEXT.LINETEXT
from LOGFILE,
LATERAL regexp_split_to_table( TEXT_CONTENT, '\r?\n' )) WITH ORDINALITY as LINETEXT