postgresql send variables to a function, casting?

Question

In one place I have

CREATE FUNCTION updateGeo2(text, float4, float4) RETURNS float AS $$

followed later by

SELECT updateGeo2('area', 40.88, -90.56);

and I get

error : ERROR:  function updategeo2(unknown, numeric, numeric) does not exist

so it doesn't know that I tried to pass in a text variable, followed by a float variable and another float variable, it sees these as "unknown, numeric and numeric", lame. How do I let it know the types I am passing in?

try this way: SELECT updateGeo2('area', (40.88)::float4, (-90.56)::float4); — sufleR, Dec 07 '12 at 19:50
@sufleR thanks , this worked, I also set `area` as ::text , you can write that as an answer if you want some points — CQM, Dec 07 '12 at 20:04
What does `SELECT version()` tell you about the version you run? I tested with 8.4.15 and it works as is. — Erwin Brandstetter, Dec 07 '12 at 21:35

score 2 · Accepted Answer · answered Dec 07 '12 at 20:05

2

try this way:

SELECT updateGeo2('area', (40.88)::float4, (-90.56)::float4);

answered Dec 07 '12 at 20:05

sufleR

2,865
17
31

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Clarify misunderstanding

First of all, this should work as is, without type cast. I tested with PostgreSQL 9.1, 9.2 and also with 8.4.15. You must be running an earlier point-release or there is some other misunderstanding (like wrong search_path). Your information is misleading.

Except for ad-hoc calls, you should always add explicit type casts anyway to disambiguate. PostgreSQL allows function overloading. If another function should be created with the signature:

CREATE FUNCTION updateGeo2(text, numeric, numeric) RETURNS text AS $$ ..

... then it would take precedence over the other one due to the default type numeric for numeric literals. Existing code might break.

If, on the other hand, you add a function:

CREATE FUNCTION updateGeo2(char(5), numeric, numeric) RETURNS text AS $$ ..

Then Postgres does not know what to do any more and throws an exception:

ERROR: function updategeo2(unknown, numeric, numeric) is not unique

Proper syntax

SELECT updateGeo2('area', '40.88'::float4, '-90.56'::float4);

Or, more verbose in standard SQL:

SELECT updateGeo2('area', cast('40.88' AS float4), cast('-90.56' AS float4));

Or, if you really wanted to avoid single quotes (and colons):

SELECT updateGeo2('area', float4 '40.88', float4 '-90.56');

This way you cast a numeric literal to data type float4 (= real) directly.
More about type casting in the manual.

(40.88)::float4 works, too, but subtly less effective. First, 40.88 is taken to be of type numeric (the default type for this numeric literal containing a dot). Then the value is cast to float4. Makes two type casts.

More about numeric constants in the manual.

thanks, do you have knowledge for this question too http://stackoverflow.com/questions/13770869/postgresql-pgplsql-increment-float-in-loop — CQM, Dec 07 '12 at 21:05

postgresql send variables to a function, casting?

2 Answers2

Clarify misunderstanding

Proper syntax