An extension is a package, a container of related SQL objects (such as functions, procedures, tables, and indexes). The advantage of packaging these related objects into a single extension, instead of using one or more scattered scripts, is that PostgreSQL will manage the whole set of objects as a single package. To some extent, it is a way of making PostgreSQL aware that these objects are related to each other, so that it can either drop or upgrade them all at once. You can still use your scripts as the backbone for an extension; you just need to make PostgreSQL aware of which files your extensions are made of.
Moreover, PostgreSQL-related tools are aware of extensions, so, for instance, when you take a backup of your database via pg_dump, no individual object from an extension will be dumped. Instead, a special command to reinstall the extension will be inserted in...