CODEWITHSUNDEEP
sqlpostgresqlplpgsqlpostgresql-8.4
CQM
CQM

Reputation: 44228

Send variables to a function, casting?

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?

Upvotes: 3

Views: 4295

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657002

Clarify misunderstanding

This should work as is, without type cast. I tested with PostgreSQL 9.1, 9.2, and even 8.4.15. You must be running an earlier point-release, or there is some other misunderstanding (like a wrong search_path).

Adding explicit type casts is never a bad idea in any case. 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:

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

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

(40.88)::float4 works, too, but subtly less efficient. Initially, 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. Two type casts.

More about numeric constants in the manual.

Upvotes: 1

sufleR
sufleR

Reputation: 2973

try this way: 

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

Upvotes: 3

Related Questions