Can't declare variable inside function on PostgreSQL

4
votes

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

1
postgresql

1 Answers

9
votes

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.