Like : Wildcard ending with variable value in pgsql

Question

I have written my pgsql block like this where I'm receiving value of var from upper part of this block:

    DO $$
declare
var integer ;
b integer;
k integer;
p integer;
m integer;
n varchar(100);
begin

k := null;
    var := (select
   count(child.relname) AS child

FROM pg_inherits
    JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
WHERE parent.relname='abc');


 while k = null loop

k := (select
   child.relname AS child

FROM pg_inherits
JOIN pg_class parent            ON pg_inherits.inhparent = parent.oid
JOIN pg_class child             ON pg_inherits.inhrelid   = child.oid
JOIN pg_namespace nmsp_parent   ON nmsp_parent.oid  = parent.relnamespace
JOIN pg_namespace nmsp_child    ON nmsp_child.oid   = child.relnamespace
   WHERE parent.relname='abc' and child.relname like '%',var);

  var := var + 1;

   end loop;

This is not the complete block. But, from block you can understand that I need to find out the partition table(value) which is ending with variable name. However, when I run this, I get below error:

 ERROR:  syntax error at or near ","
LINE 34: ...RE parent.relname='abc' and child.relname like '%',var);

I tried with double %% sign(one for wild card and another to get variable value then I got below error:

    ERROR:  syntax error at or near ","
 LINE 34: ...E parent.relname='abc' and child.relname like '%%',var);

could anyone please let me know if there is any other way to access a pgsql variable value with wildcard.

Answer 1

You need to use string concatenation:

... child.relname LIKE ('%' || var)