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.

A.H. · Accepted Answer · 2011-10-06 13:45:52Z

13

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.

edited Oct 6, 2011 at 13:45

answered Oct 6, 2011 at 12:19

A.H.

66.8k16 gold badges97 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shiver Over a year ago

Hi A.H, I changed language to plpgsql and got this error:ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database.

A.H. Over a year ago

Added some explanation in the post.

Erwin Brandstetter Over a year ago

@Shiver: Since PostgreSQL 9.0, language PL/pgSQL is installed by default. Additionally, you can: CREATE OR REPLACE LANGUAGE plpgsql; to be sure.

Shiver Over a year ago

Great thanks A.H I run create command and managed to create the function. Thanks guys for all the help.

A.H. Over a year ago

Please "vote" and "accept" the usefull answers as described in the FAQ.

mu is too short · Accepted Answer · 2011-10-06 17:37:51Z

1

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;

answered Oct 6, 2011 at 17:37

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related