0

I'm looking for a way to write a recursive query, but I'm getting an error. "SQL Error [42601]: ERROR: syntax error at or near "with recursive" Position: 287"

query result "select * from tbl1" this is a list of numbers: 110,111,112... Each number must be inserted into a row "s_table_sd_||r.cut_name||_db_tmp.e_pack_log"

i use:

with tbl1 as (
  select schema_name
        ,rtrim( ltrim( schema_name,'s_table_sd_'),'_db_tmp') as cut_name
  from information_schema.schemata s 
  where schema_name like '%db_tmp'
        and rtrim( ltrim( schema_name,'s_table_sd_'),'_db_tmp') <> ''

)

with recursive r as (
  select cut_name
  from tbl1
  union
  select * 
  FROM s_table_sd_||r.cut_name||_db_tmp.e_pack_log 
  where s_table_sd_||r.cut_name||_db_tmp.e_pack_log like '%'||r.cut_name||'%'
  )
  select * from r

Any ideas how to fix the request?

3
  • You need to put the recursive at the top with the first CTE and you have to separate the CTEs with ,: stackoverflow.com/a/35249370
    – user1822
    Commented Jan 11, 2023 at 6:57
  • FROM s_table_sd_||r.cut_name||_db_tmp.e_pack_log makes no sense to me. You cannot concatenate names of tables.
    – user1822
    Commented Jan 11, 2023 at 6:58
  • That does not look like a recursive query at all. Commented Jan 11, 2023 at 12:28

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.