PostgreSQL – ERROR: ‘sql’ is Not a Known Variable

plpgsqlpostgresqlpostgresql-9.4scriptingunicode

This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running.

I'm invoking the cobertura.sql file using the following sample CLI syntax:

psql -h localhost -U myUser -f cobertura.sql myDB

But it complains with the following error:

psql:cobertura.sql:29: ERROR:  "sql " is not a known variable
LINE 14: sql := format('insert into cobertura_tmp select count(*) as ... cobertura.sql file:

DO language plpgsql $$
declare 
    eq  record;
    sql varchar;
BEGIN

create table if not exists cobertura_tmp  (num integer, realtime char(1), lat numeric, lng numeric);

truncate table cobertura_tmp;
for eq in select imei_equipo as imei  from cliente_avl_equipo where id_cliente in (select id from cliente where nombre ilike '%enangab%') limit 3

loop

sql := format('insert into cobertura_tmp select count(*) as num, tipo as realtime, round(CAST(latitud as numeric), 4) as lat ,round(CAST(longitud as numeric), 4) as lng   from reports.avl_historico_%s where latitud between -38.67405472 and -36.75131149 and longitud between  -73.08429161 and -69.65333954 group by tipo, round(CAST(latitud as numeric), 4),round(CAST(longitud as numeric), 4)', eq.imei);

execute sql;

end loop;

update cobertura_tmp set num= -1* num where realtime='S';

create table if not exists cobertura_tmp_resumen  (num integer, lat numeric, lng numeric);
truncate cobertura_tmp_resumen;
--    select sum(num) as num , lat, lng into cobertura_tmp_resumen from cobertura_tmp group by lat,lng;

--    drop table if exists cobertura_tmp;

END;
$$;

The same script runs remotely and smoothly from Mac OSX using Postico Version 1.3.2 (2318).

QUESTION: Why isn't it finding the sql variable that is in the declare section?

Best Answer

Your error message says:

psql:cobertura.sql:29: ERROR:  "sql " is not a known variable
LINE 14: sql := format('insert into cobertura_tmp select count(*) as ...

Look closely: "sql ", not "sql"

That means you have a sneaky, invisible character right after "sql" instead of an innocent space character. It's not in your question, probably got lost in translation when you copied & pasted it in your question here. Your original code contains something like:

sql　:= format ...

Do you see it? No? Because one cannot see it. (You might notice the space is a tad bit wider in this particular case - if your browser, font and character set produce the same result as mine.) It's an "ideographic space", Unicode U+3000, HTML &#12288. Just a random example. There are various characters like that in Unicode. Replace it with a simple space to fix your problem.

dbfiddle here

I hate this about Unicode, too many characters that mostly just confuse people ...

For a quick test:

SELECT ascii(' ');   -- 32    -- plain space
SELECT ascii('　');  -- 12288 -- not a plain space
SELECT '　' = ' ';   -- f     -- not the same!

A quick test whether there are any non-ASCII characters in a string:

SELECT octet_length(string) = char_length(string)  -- f
FROM  (SELECT text 'sql　:= format' AS string) t;

Assuming encoding UTF8, non-ASCII characters occupy 2-4 bytes, while plain ASCII characters occupy a single byte. octet_length() returns the

Number of bytes in string

while char_length() (same as length()) returns the number of characters. For all-ASCII, both return the same. If octet_length() returns a higher number, you have suspicious characters. Does not have to mean anything. Any accented character in a string is good for it.

Or use an editor that can highlight suspects.

Aside, while being at it, your plpgsql code would be considerably more efficient like this:

DO
$$
DECLARE
   _imei text;
   _sql  text;
BEGIN

IF to_regclass('pg_temp.cobertura_tmp') IS NULL  -- if tmp table does not exist
THEN
   CREATE TABLE cobertura_tmp (
      num      integer
    , realtime char(1)
    , lat      numeric
    , lng      numeric
    );
ELSE
   TRUNCATE TABLE cobertura_tmp;
END IF;

FOR _imei IN
   SELECT e.imei_equipo::text  -- imei is NOT NULL & *safe* against SQL injection
   FROM   cliente c
   JOIN   cliente_avl_equipo e ON e.id_cliente = c.id  -- assuming c.id is UNIQUE
   WHERE  c.nombre ILIKE '%enangab%'
   LIMIT  3
LOOP
   _sql := format(
   $s$
   INSERT INTO cobertura_tmp (num, realtime, lat, lng)
   SELECT count(*)::int * (CASE WHEN a.tipo = 'S' THEN -1 ELSE 1 END) -- AS num
        , a.tipo                         -- AS realtime
        , round(a.latitud::numeric, 4)   -- AS lat
        , round(a.longitud::numeric, 4)  -- AS lon
   FROM   reports.avl_historico_%s a
   WHERE  a.latitud  BETWEEN -38.67405472 AND -36.75131149
   AND    a.longitud BETWEEN -73.08429161 AND -69.65333954
   GROUP  BY 2,3,4
   $s$
 , _imei);

   EXECUTE _sql;
END LOOP;

/* integrated above to avoid costly extra update:
UPDATE cobertura_tmp
SET    num = -1 * num
WHERE  realtime = 'S';
*/

-- ... etc

END
$$;

Many little details, but that's not the topic of the question. I doubt you need a temporary table at all ...

Try it if you want to improve the code. Related:

Related Question