PostgreSQL: ERROR operator does not exist: || character varying

Question

I need to check the condition within function using string_agg() function and need to assign it to variable. After assigning I need to execute the variable with value.

Example:

create or replace function funct1(a int,b varchar)
returns void as
$$
declare
        wrclause varchar := '';
        sqlq varchar ;
        t varchar;
begin
     IF (b IS NOT NULL ) THEN 
        wrclause := 'AND b IN ('|| b || ')';
     END IF;


     sqlq := string_agg('select *, abcd as "D" from ' ||table_namess,' Union all ') as namess
     from tablescollection2 ud
     inner join INFORMATION_SCHEMA.Tables so on ud.table_namess = so.Table_name
     WHERE cola NOT IN (SELECT cola FROM tablet WHERE colb = || a  ||) || wrclause; /* Error occurred here at  = || a */

     raise info '%',sqlq;

     execute sqlq into t;

     raise info '%',t;
end;
$$
language plpgsql;

Calling Function:

select funct1(1,'1,2,3');

Error:

ERROR:  operator does not exist: || integer

score 1 · Accepted Answer · edited May 23 '17 at 10:26

|| is an operator for catenating two pieces of text, it requires you to have text (or something convertible to text) both before and after the operator, like so:

select 'a' || 'b'
select 'a' || 3

So while these seem to be valid:

wrclause := 'AND b IN ('|| b || ')';
sqlq := string_agg('select *, abcd as "D" from ' ||table_namess,' Union all ') as namess

This is definitely not:

WHERE cola NOT IN (SELECT cola FROM tablet WHERE colb = || a  ||) || wrclause;

What were you trying to achieve here?

It looks like you may be trying to construct a query dynamically. You need to remember that you cannot mix free text with SQL and expect Postgres to sort it out, no programming or query language does that.

If that's your intention, you should construct the query string first in its entirety (in a variable), and then call EXECUTE with it to have it interpreted.

Have a look at these:

Yes! I want the reason? – Sarfaraz Makandar Jul 11 '14 at 11:26 — Sarfaraz Makandar, Jul 11 '14 at 11:26

pozs · Answer 2 · 2014-07-11T13:20:44.990

This piece contains the syntax error

... IN (SELECT cola FROM tablet WHERE colb = || a  ||) || ...

PostgreSQL can understand this, but will try to search for unary prefix (and a postfix) || operator, which are not exist by default (they can be created however, but the error message says, that's not the case)

Edit:

F.ex. these are valid (predefined) unary operators on numbers:

SELECT |/ 25.0, -- prefix, square root, result: 5.0
       5 !,     -- postfix, factorial, result: 120,
       @ -5,    -- prefix, absolute, result: 5
       @ -5 !;  -- mixed, result: 120

PostgreSQL: ERROR operator does not exist: || character varying

2 Answers2