SQL Error [42883]: ERROR: operator does not exist: text || boolean when calling Redshift Stored Proc-CodePudding

new to Redshift and I'm having an issue with calling a stored proc from redshift. I created a new SP based on an existing once since there's an additional parameter. The parameter is BOOL type so I want to pass in true, false or null as my $8 param, all the other params are varchar. The SP was persisted and saved but when I make the call I get the following error message:

SQL Error [42883]: ERROR: operator does not exist: text || boolean
  Hint: No operator matches the given name and argument type(s). You may need to add explicit type casts.
  Where: SQL statement "SELECT  'select     
        code.title,
        sum(tax_inclusive_amount) as "Total spend"      
        from #temp_invoice_combined  join dw.codes code
        on code.code = item_classification_code
        where code."key" in (select "key" from dw.codes where "parent key" in
 (select key from dw.codes where "parent key" in (select "key" from dw.codes where "parent key" is null))) 
and currency in ' ||  $1  || ' 
and days in ' ||  $2  || ' 
and account = ' ||  $3  || '
and supplier in ' ||  $4  || ' 
and product_id in ' ||  $5  ||' 
and item_codes in ' ||  $6  ||' 
and country in ' ||  $7  || '
and format = ' ||  $8  || ' 
group by code.title order by 2 desc limit 10'"
PL/pgSQL function "top10_category" line 13 at open

The temp table does have the invoice_format column.

The parameter list for the call looks like this:

vstart_date date, vend_date date, vcurrency varchar, vdays varchar, vaccount_id varchar, vsupplier varchar, vproduct_id varchar, vcodes varchar, vcountry varchar, vformat bool, rs_out refcursor

It is probably something very small that I'm missing, it does say that an explicit cast is needed but I tried in the parameter like so $8::boolean but that didn't work either. So not sure where to cast here.

The column in the view I am using in the select statement is also bool type.

Any explanation regarding this will be appreciated.

CodePudding user response：

You can only concatenate strings together and you are trying to concatenate a string with a Boolean.