2

I'm trying to get some result like this:

rownumber | value
    1     |   a
    2     |   b
    3     |   c

Without any tables I'm doing something like this:

WITH RECURSIVE t(rownumber, value) AS (
select 1, regexp_split_to_table('a, b, c', ',')
UNION ALL
SELECT rownumber+1, regexp_split_to_table('a, b, c',',') FROM t 
)
SELECT * FROM t limit (select count(*) from regexp_split_to_table('a, b, c', ','));

But the results are not as expected.

The reason I'm doing this is because the value 'a, b, c' should be some variable.

In Oracle the SQL looks like this:

SELECT value  FROM (
        SELECT ROWNUM AS rownumber, trim(REGEXP_SUBSTR('a, b, c','[^,]+', 1, LEVEL)) AS value
        FROM DUAL CONNECT BY trim(REGEXP_SUBSTR('a, b, c', '[^,]+', 1, LEVEL)) IS NOT NULL 
      )

and It works.

What am I doing wrong in Postgresql?

some_coder
  • 23
  • 1
  • 2

1 Answers1

2

This is happening because regexp_split_to_table returns a table and not a single value for each rownumber.

Maybe a different approach will work? For example, this gives me the output you were after:

SELECT  regexp_split_to_table('a,b,c', ',')   as value, 
        rank() over(order by regexp_split_to_table('a,b,c', ','))   as rownumber

EDIT: The above will reorder the results which may not be what you want. The following will preserve the ordering:

WITH T as
(
SELECT  regexp_split_to_table('d,a,b,c', ',')   as value
)

SELECT  row_number() over() as rownumber,
        value

FROM    t
kaisquared
  • 406
  • 4
  • 10
  • Thank you, that worked for me! Just added an outter select because I needed to filter by the rownumber from the table t like `select value from ([your bottom query]) as table where rownumber = [my_number]` – some_coder May 02 '16 at 13:27
  • No worries, mate. Glad you're all sorted =). – kaisquared May 03 '16 at 02:52