使用哪种语言编写 PostgreSQL 脚本？

Which language to use for scripting PostgreSQL?

提问by thomaspaulb

I am about to embark on a PostgreSQL project for a client. They want to develop a huge professional database with many complex joins, so after consideration I have chosen to go with PostgreSQL over MySQL.

我即将为客户着手一个 PostgreSQL 项目。他们想开发一个具有许多复杂连接的大型专业数据库，因此经过考虑，我选择使用PostgreSQL 而不是 MySQL。

An important consideration is how to effectively interface to the database with scripts. Currently, the client uses about a million scripts to import and reshape data to their needs, but uses no database (unless you consider CSV files to be a database). With the arrival of a database structure with queries and views, the need for scripts will be less, but importing will still need to be done often, and exporting/reporting as well. For me the ideal end result would be a series of standardized scripts, preferably with a web interface, so that the client can perform regular tasks quickly and error-free with a click of the button.

一个重要的考虑是如何使用脚本有效地连接到数据库。目前，客户端使用大约一百万个脚本来根据需要导入和重塑数据，但不使用数据库（除非您将 CSV 文件视为数据库）。随着带有查询和视图的数据库结构的出现，对脚本的需求将会减少，但仍然需要经常进行导入，以及导出/报告。对我来说，理想的最终结果是一系列标准化的脚本，最好带有 Web 界面，这样客户端只需单击按钮即可快速且无错误地执行常规任务。

My question is which scripting approach will be most appropriate. Probably any scripting language with a Postgres or an ODBC plugin would suffice, but I am looking to make a smart choice for the long term. Does anybody have experience with this? Does Postgres offer an internal scripting language, and is it easy to build a GUI for that? Are there any standardized tools available for importing/exporting, and are they customizable enough to allow standardization of tasks to click-level? How about PHP or perl?

我的问题是哪种脚本方法最合适。可能任何带有 Postgres 或 ODBC 插件的脚本语言就足够了，但从长远来看，我希望做出明智的选择。有没有人有这方面的经验？Postgres 是否提供内部脚本语言，并且为此构建 GUI 是否容易？是否有任何可用于导入/导出的标准化工具，并且它们的可定制性是否足以允许将任务标准化到点击级别？PHP 或 perl 怎么样？

Thanks in advance. Any tips, resources, puzzled looks or pitiful gestures will be truly appreciated ;-)

提前致谢。任何提示、资源、困惑的表情或可怜的手势将不胜感激;-)

采纳答案by cope360

Since you are talking about scripts that expressly just manipulate the database, I would start with the most native tools.

由于您在谈论专门操作数据库的脚本，因此我将从最原生的工具开始。

• SQL and PL/pgSQL stored functions for manipulating and processing data
• COPY FROMand COPY TOfor importing from and exporting to flat files
• An ETL tool for any reshaping that can't be handled with the above

• 用于操作和处理数据的 SQL 和 PL/pgSQL 存储函数
• COPY FROM以及COPY TO用于从平面文件导入和导出
• 一个 ETL 工具，用于无法用上述方法处理的任何整形

Now, you want to provide some easy web interface for interfacing with these scripts. Here the best language is probably the one you or your team already knows. All major languages have Postgres drivers. The language you choose will have very little impact if you keep your data manipulation tasks at the database layer.

现在，您希望提供一些简单的 Web 界面来与这些脚本交互。这里最好的语言可能是您或您的团队已经知道的语言。所有主要语言都有 Postgres 驱动程序。如果您将数据操作任务保留在数据库层，那么您选择的语言将产生很小的影响。

One thing to consider is how long the typical script will take to execute. If it is more than a few minutes, then I suggest decoupling it from the web interface. In that case, the web interface should allow the user to queue the script to start so that the server can run it independent of the web request cycle.

需要考虑的一件事是执行典型脚本需要多长时间。如果超过几分钟，那么我建议将其与Web界面解耦。在这种情况下，Web 界面应允许用户将脚本排入队列以启动，以便服务器可以独立于 Web 请求周期运行它。

回答by Micha? Niklas

I use Python/Jython to connect to PostgreSQL and do various things.

我使用 Python/Jython 连接到 PostgreSQL 并做各种事情。

Pluses:

优点：

• there is pl/pythonso you can use Python from PostgreSQL
• you can create "standalone" programs using Python DB API, there is docabout database programming, and PostgreSQLin details
• you can use Jython if you like JVM environment, especially JDBC driver

• 有pl/python所以你可以使用 PostgreSQL 中的 Python
• 您可以使用Python DB API创建“独立”程序，有关于数据库编程的文档，以及PostgreSQL的详细信息
• 如果您喜欢 JVM 环境，尤其是 JDBC 驱动程序，则可以使用 Jython

Examples of usage:

用法示例：

• converting PDF, MS Word and OopenOffice documents saved in BLOBs to text to index it
• importing data from various sources, not only cvs; Python is really strong at converting text data
• testing drivers while some our apps are native and use ODBC or JDBC drivers and Jython can work with both drivers (for ODBC there is JDBC-ODBC bridge)

• 将保存在 BLOB 中的 PDF、MS Word 和 OopenOffice 文档转换为文本以对其进行索引
• 从各种来源导入数据，不仅仅是cvs；Python 在转换文本数据方面非常强大
• 测试驱动程序，而我们的一些应用程序是本机的并使用 ODBC 或 JDBC 驱动程序，而 Jython 可以使用这两种驱动程序（对于 ODBC，有 JDBC-ODBC 桥接器）

On my other posts on SO you can see I use Python/Jython with other databases as Oracle and Informix. For example I created tools that dumps some info from database schema so I can easily compare databases in test and production environment.

在我关于 SO 的其他帖子中，您可以看到我将 Python/Jython 与其他数据库（如 Oracle 和 Informix）一起使用。例如，我创建了从数据库模式转储一些信息的工具，以便我可以轻松地比较测试和生产环境中的数据库。

回答by Sualeh Fatehi

SchemaCrawlerfor PostgreSQL allows you to script against a database using JavaScript.

SchemaCrawlerfor PostgreSQL 允许您使用 JavaScript 针对数据库编写脚本。

回答by Evan Carroll

I use Perl. I would suggest it too. As far as database scripting languages that postgres offers: pl/perlis far more developed than pl/php, and pl/perlubrings CPAN to Postgres.

我使用 Perl。我也会建议它。就 postgres 提供的数据库脚本语言而言：pl/perl比 更发达pl/php，并将pl/perluCPAN 带到 Postgres。

You still have pl/sql(great for small stuff), and pl/pgsql(has its applications too).

你仍然有pl/sql（非常适合小东西）和pl/pgsql（也有它的应用程序）。

Unfortunately, CSV support on Postgres is kind of crufty, I'm going to start a Perl project very soon though to fix this (probably within the next month). Currently, you almost have to use pgloader which (imho) has a kludgey syntax. Perl has Text::CSV_XS, which is a godsend for processing CSVs.

不幸的是，Postgres 上的 CSV 支持有点粗糙，我将很快启动一个 Perl 项目来解决这个问题（可能在下个月内）。目前，您几乎必须使用 pgloader，它（恕我直言）具有笨拙的语法。Perl 有Text::CSV_XS，这是处理 CSV 的天赐之物。

Perl historically has done database access and use much better than PHP. I still believe it is a good way ahead of PHP. PHP still lacks an asynchronous framework, and is rather limited to the web. PHP's strong points tend to be much more centered around non-technical features of the language, namely the learning curve, and shared host portability.

Perl 过去在数据库访问方面做得比 PHP 好得多。我仍然相信它是领先于 PHP 的好方法。PHP 仍然缺乏异步框架，并且相当局限于 Web。PHP 的优势往往更多地集中在语言的非技术特性上，即学习曲线和共享主机可移植性。