CODEWITHSUNDEEP
postgresqlpostgresql-9.2
Oto Shavadze
Oto Shavadze

Reputation: 42773

Convert function argument into function

I have this code

CREATE OR REPLACE FUNCTION test_func( str CHARACTER VARYING ) RETURNS CHARACTER VARYING 
AS $$

BEGIN
    str := CAST(str AS INTEGER);

    IF  str > 4 THEN
        RETURN 'YES';
    ELSE
        RETURN 'NO';
    END IF;

END;
$$
LANGUAGE plpgsql;

Then, when call this function SELECT test_func('9')

returned error: operator does not exist: character varying > integer

Why this hapened? why str is not converted as INTEGER ?

Upvotes: 0

Views: 89

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657022

This is broken by design.

If the value handed to the function is a valid integer, the parameter should be of type integer to begin with. Else the cast inside the function body will raise an exception sooner or later.
It should be:

CREATE OR REPLACE FUNCTION test_func(str integer) ...

Then you don't need any casting.

And - as long as it's simple as the test case - use an SQL function with a CASE statement or a plain SQL query:

CREATE OR REPLACE FUNCTION test_func(str int)
  RETURNS text AS
$$
SELECT CASE WHEN str > 4 THEN 'YES' ELSE 'NO' END;
$$
LANGUAGE sql;

Note that you can't refer to parameter names in SQL functions before Postgres 9.2. Use the positional parameter $1 in older versions.

Upvotes: 2

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125284

It did cast to integer as you asked it to. But then it did an automatic conversion to varchar as that is the str's type. Do it like this:

IF str::int > 4 THEN

Upvotes: 1

Related Questions