Geek Answers Handbook

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

I am very new to postgres. I got this error while trying 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;

Error

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

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

+4
source share
2 answers

This is unsuitable:

 LANGUAGE sql

use this instead:

 LANGUAGE plpgsql

The syntax you are trying to use is not pure SQL, but the procedural language PL / pgSQL. In PostgreSQL you can install different languages, and PL / pgSQL is only primus inter pares for that matter. It also means that you may receive an error message that this language is not installed. In this case, use

 CREATE LANGUAGE plpgsql;

which acts on him. Depending on the version of PostgreSQL, you may need superuser privileges to take this step.

Good luck.

+9
source

Not only are you using the wrong language (as noted by AH), but there is the keyword returns , you want return . You might want to use a different delimiter to avoid string literal problems in your functions, $$ pretty common. I think your function should look something 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;
+1
source

More articles:


All Articles