What language to use for writing PostgreSQL?

Question

I am about to start a PostgreSQL project for a client. They want to create a huge professional database with many complex joins, so after reviewing I decided to go with PostgreSQL via MySQL .

An important consideration is how to effectively interact with the database with scripts. Currently, the client uses about a million scripts to import and convert data to their needs, but does not use a database (if you do not consider CSV files as a database). With the advent of the database structure with queries and views, the need for scripts will be less, but import will still have to be done often, as well as export / reporting. For me, the ideal end result is a series of standardized scripts, preferably with a web interface, so that the client can perform normal tasks quickly and without errors with the click of a button.

My question is which scenario approach would be most appropriate. Any scripting language with Postgres or the ODBC plugin will probably suffice, but I want to make a reasonable choice in the long run. Does anyone have any experience with this? Does Postgres offer an internal scripting language, and is it easy to create a graphical interface for this? Are there any standard tools for import / export and can they be customized so that the standardization of tasks reaches the click level? What about PHP or perl?

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

Answer 1

Since you are talking about scripts that directly manipulate the database, I would start with the most popular tools.

• Stored SQL and PL / pgSQL functions for managing and processing data
• COPY FROM and COPY TO for import and export to flat files
• ETL tool for any conversions that cannot be processed using the above

Now you want to provide a simple web interface for interacting with these scripts. Here is the best language, 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 processing tasks at the database level.

You might think how long a typical script will run. If it is more than a few minutes, I suggest disconnecting it from the web interface. In this case, the web interface should allow the user to run the script queue so that the server can run it regardless of the web request cycle.

Answer 2

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

Advantages:

• there is pl / python so you can use Python from PostgreSQL
• you can create "stand-alone" programs using the Python API , there is a doc about database programming and PostgreSQL more
• you can use jython if you like the jvm environment especially the jdbc driver

Examples of using:

• converting PDF, MS Word and OopenOffice documents saved in BLOB into text for indexing
• import data from different sources, not just cvs; Python is really strong at converting text data.
• driver testing, while some of our applications are native and use ODBC or JDBC drivers, and Jython can work with both drivers (there is a JDBC-ODBC bridge for ODBC)

In my other posts on SO, you can see that I am using Python / Jython with other databases like Oracle and Informix. For example, I created tools that extract some information from a database schema, so I can easily compare databases in a test and production environment.

Answer 3

I am using Perl. I would also suggest. Regarding the database scripting languages ​​that postgres offers: pl/perl much more advanced than pl/php , and pl/perlu returns CPAN to Postgres.

You still have pl/sql (great for small things) and pl/pgsql (also has its own applications).

Unfortunately, Postgres CSV support is pretty cool, I'm going to start the Perl project very soon, but fix it (maybe within the next month). You should almost now use pgloader, which (imho) has kludgey syntax. Perl has Text::CSV_XS , which is a godsend for CSV processing.

Perl has historically made database access and uses much better than PHP. I still think this is a good way to go before PHP. PHP still does not have an asynchronous structure and is quite limited for the Internet. PHP strengths are typically much more focused around non-technical language features, namely the learning curve and overall host portability.

Answer 4

SchemaCrawler for PostgreSQL allows a script to use a database using JavaScript.