PostgreSQL: Perl procedures with PL/pgSQL Part 2.

By Mark Nielsen

Introduction
Perl script to create tables, procedures, backup tables, and sequences.
Executing the Perl script
Considerations to explore.
Conclusion
References

Introduction

After dealing with installing PostgreSQL, Perl, and embedding Perl in PostgreSQL, I wanted a standard way to create tables, sequences, stored procedures, and backup tables. Perhaps other people have nice GUI solutions to do this, but I haven't seen any. I would like it if someone would work with me to create a GUI interface to achieve what I am doing here. My goals are:

Always backup data no matter what happens.
Always use stored procedures to insert, update, delete, copy, or to do anything that changes data on the tables. One should even create stored procedures to select data.
Have a Perl script create tables, sequences, backups tables, and the stored procedures to manipulate the data.
Have the stored procedures clean data using Perl.
Backup data if someone runs the Perl script on a live system.
There should be an active column in the table so that you can specify active or inactive rows in the table. We create a view which views active rows of a table.
Stored procedures should record date created and date last updated.
All rows have a unique id. Even if we choose not to use them, they will still have them. It is not always good to use oid to get unique rows.
Be able to delete inactive rows with a purge procedure. The delete procedure just makes it inactive. Also, unpurge data with the latest purged data for a unique id. This is cool.
All negative numbers returned from pl/sql procedures are considered failures. All positive numbers (including 0) are considered to be successes in the fact nothing errored out. They are either 0, in which nothing happened, or something greater than 0 which indicates the number of items affected or a id number.

My future goals include:

Creating a GUI interface. Preferrably one that is not dependent on GNOME or KDE libraries but Python. You can create Python binaries easily, so I would prefer Python/TK.
Allow the GUI design to make changes to live tables by either:
- Making updates that really happen with full effects. Some changes con't allow all options (at least in the past).
- Creating a new table, and dumping all the data from the old table into the new one while locking the old table.
Record all database changes to review history.

Perl script to create tables, procedures, backup tables, and sequences.

Here is the Perl script I use. You can also get a copy here Create_Functions.pl.txt.

Rename the perl script "Create_Functions.pl.txt". Here are the things needed to get it to work:

You must change the options above the dotted line.
You must have PostgreSQL installed with Perl embedded in it.
Enter the command "psql template1". Then type "create database testdatabase;" or whatever you named your database. Press enter. If you have errors along the way, you haven't set up the permissions yet. Login in as root, then execute "su -l postgres". Then type "createuser" and press enter. This will create a user in your postgresql database. Enter in the username and give the username full privledges. Then try again with your normal account.

Executing the Perl script

You will need some more files. The Tables.txt file.

You can use my file as an example, but I suggest to modify it for your own needs. It is simulated to make three tables. One containing userame and passwords, and the other associating a username to a list of contacts. Another file you will need is Generic.fun

and lastly Custom.sql.

After you save the perl script, execute "chmod 755 Create_Functions.pl" and then "./Create_Functions.pl". That should do it.

If you have installed PostgreSQL and Perl correctly, and you have setup the database and your account has permissions to that database, then everything should have worked fine.

Considerations to explore.

I would like to test TCL, Python, and other languages as well for stored procedures. If you are using MySQL, and I don't believe it has stored procedures, you may want to consider PostgreSQL if you like the style I mentioned. A nice GUI application to create tables and make changes to tables would be nice. Lastly, examples of how to connect to the database server to use these stored procedures (using Perl, Python, PHP, TCL, C, etc) would be nice.

Conclusion

The combination of PostgreSQL and Perl rocks. I can use Perl for three things, stored procedures, to setup my database, and to make Perl modules for Apache that connect to the PostgreSQL database. Similar stuff can be accomplished with other programming languages like Python, TCL, and others. I want to try Python at some point when it gets out of beta for PostgreSQL.

All database servers should use procedures exclusively for changing data. You could even argue that you should make custom stored procedures for selecting data as well. The reason why this is so important is because the web programmer (or other type of programmer) doesn't have to know anything about how to manipulate the data. They just submit variables to procedures. This lets the web programmer use any programming language he/she wants to without changing the behaviour of the database. The database and how you use it becomes abstract.

One stupid thing my perl script does is execute the custom sql code for each table. This s very bad. I will have to go back and fix it later. You may want to test my stuff out with these commands:

select sql_account_insert();
select sql_account_delete(1);
select sql_account_insert();
select sql_account_update(2,'mark','nielsen');
select sql_account_purge();
select sql_account_unpurge();
select * from account_backup;
select sql_account_delete(2);
select sql_account_insert();
select sql_account_update(1,'john','nielsen');
select sql_account_purge();
select * from account_backup;

References

My Previous PostgreSQL article.
If this article changes, it will be available here http://www.gnujobs.com/Articles/22/Perl_PostgreSQL2.html

Mark works as an independent consultant donating time to causes like GNUJobs.com, writing articles, writing free software, and working as a volunteer at eastmont.net.