How to use variable in the select query PostgreSQL

0 votes

0 answers

179 views

postgresql recursive cursors concat variable

                          I'm looking for a way to write a recursive query, but I'm getting an error.
"SQL Error : 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?
                        

Asked by AleksTr (1 rep)

Jan 11, 2023, 06:47 AM
Last activity: Jan 11, 2023, 06:47 AM

How to use variable in the select query PostgreSQL

Related Questions