Dynamic Postgress Query; ERROR: operator does not exist: character varying = integer

0
votes

I know this is a simple/foolish question, but I am struggling here . . .

Running: Postgress 9.3 on OSX installed with Brew Function below gives me this

ERROR: operator does not exist: character varying = integer

HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

QUERY: SELECT substring(address_postal_code for 5) FROM provider_nodes WHERE npi = $1

CONTEXT: PL/pgSQL function test(integer) line 9 at EXECUTE statement

npi_id is stored as varchar in provider_nodes table but is a number. Anyway around this or do I need to change the type?

CREATE OR REPLACE FUNCTION test(npi_id varchar) RETURNS RECORD AS $$
DECLARE 
zip RECORD;
ptlong RECORD;
ptlat RECORD;
result RECORD;

BEGIN
EXECUTE 'SELECT substring(address_postal_code for 5) FROM provider_nodes WHERE npi = $1' INTO zip;
EXECUTE 'SELECT longitude FROM zctas WHERE zcta=$1' INTO ptlong USING zip;
EXECUTE 'SELECT latitude FROM zctas WHERE zcta=$1' INTO ptlat USING zip;
EXECUTE 'SELECT state, zctas FROM zctas WHERE geom &&   ST_expand(ST_transform(ST_PointFromText("POINT(" || ptlong || " " || ptlat || ")", 4269),32661), 16093) AND ST_distance((ST_transform(ST_PointFromText("POINT(" || ptlong || " " || ptlat || ")", 4269),32661),geom) < 16093' INTO result;
RETURN RESULT;
END;
$$ LANGUAGE plpgsql;
1
sqldynamicplpgsqlexecute

1 Answers

0
votes

When you use a parameters inside dynamic SQL, you have to use a USING clause. You miss it in first statement:

EXECUTE 'SELECT substring(address_postal_code for 5) FROM provider_nodes WHERE npi = $1' INTO zip USING npi_id;

But using dynamic SQL (statement EXECUTE) is bad idea in this case. You can write

zip := (SELECT substring(p.address_postal_code for 5) 
           FROM provider_nodes p
          WHERE p.npi = npi_id);