I am writing function in PostgreSQL but it doesn't allow me to declare variable inside it. Here is the function.
CREATE FUNCTION clean_emp() RETURNS void AS
$func$
DECLARE cnt varchar;
$func$ LANGUAGE SQL;
Error Message
ERROR: syntax error at or near "varchar"
SQL state: 42601
Character: 66
It is not surprise. The language SQL doesn't support variables. You have to use the language plpgsql.
CREATE OR REPLACE FUNCTION clean_emp()
RETURNS void AS $$
DECLARE cnt varchar;
BEGIN
END;
$$ LANGUAGE plpgsql;
See more in documentation http://www.postgresql.org/docs/current/static/plpgsql.html.
PostgreSQL has more languages for writing function. The SQL language is perfect for one line single statement macros. The PLpgSQL is classical native language similar to Oracle's PL/SQL with embedded SQL.
You can declare local variables with :=, = in DECLARE clause as shown below. *My answer explains how to declare local variables in detail:
CREATE FUNCTION my_func() RETURNS INTEGER AS $$
DECLARE
value1 INTEGER := 1; -- Here
value2 INTEGER = 2; -- Here
value3 INTEGER DEFAULT 3; -- Here
BEGIN
RETURN value1 + value2 + value3;
END;
$$ LANGUAGE plpgsql;
