postgres error syntax error at or near "int" when creating a function

Question

I am very new to postgres. I got this error when try to run the following script:

CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS
'DECLARE result int;
BEGIN
    SELECT count(*) into result from tbldealercommissions
    WHERE 
    txtdealercode = $1;

    if result < 1 then returns 1; 
    else returns 2 ;
    end if;
END;
    '
LANGUAGE sql VOLATILE;

The error is

ERROR:  syntax error at or near "int"
LINE 3: 'DECLARE result int;

not sure what cause this error. Any help is appreciated.

Answer 1

This is unsuitable:

LANGUAGE sql

use this instead:

LANGUAGE plpgsql

The syntax you are trying to use is not pure SQL language but the procedural PL/pgSQL language. In PostgreSQL you can install different languages and PL/pgSQL is only primus inter pares in that regard. This also means that you might get the error message, that this language is not installed. In that case use

CREATE LANGUAGE plpgsql;

which actives it. Depending on the version of PostgreSQL you might need superuser rights to do this step.

Have fun.

Answer 2

Not only are you using the wrong language (as noted by A.H.) but there is returns keyword, you want return. You might want to use a different delimiter to avoid running into problems with string literals in your functions, $$ is pretty common. I think your function should look more like this:

CREATE OR REPLACE FUNCTION xyz(text) RETURNS INTEGER AS $$
DECLARE result int;
BEGIN
    select count(*) into result
    from tbldealercommissions
    where txtdealercode = $1;

    if result < 1 then return 1; 
    else return 2;
    end if;
END;
$$ LANGUAGE plpgsql VOLATILE;