sqldatabasepostgresqllocal-variables

Can't declare variable inside function on PostgreSQL

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
Solution

  • 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.