Unable to save a stored procedure which contains a temporary table creation and update query
You have to specify LANGUAGE plpgsql rather than LANGUAGE sql.
The function does not consist of only SQL statements, and other than for PL/pgSQL, the SQL language validator parses the SQL statements and checks them for validity.