Re: How should I get started? - Mailing list pgsql-general

Hi Leif;
----- Original Message -----
From: "Leif K-Brooks" <eurleif@ecritters.biz>
> I've been programming web applications in PHP/MySQL for a few years.
> It's worked decently, but I've been getting annoyed with the lack of
> more advanced features lately. After some reading, I've decided to
> switch to Perl/PostgreSQL.

Hmm...  I program in Perl, PHP and Python with PostgreSQL as my preferred
backend.  One caution I would give you is that Perl and PHP have many
surface similarities which are misleading.  I would suggest that you learn
PostgreSQL and Perl separately.  BTW, I tend to use PHP for my web apps and
Perl for utilities, but I relialize that different applications may handle
the languages differently.  Some people think that PHP is a sort of
Perl-Lite, but I disagree-- they are just too different to compare like
this.

Furthermore, PostgreSQL allows you to approach the database backend as an
active layer in your application rather than a simple light-weight
information store (MySQL).  This allows you to write thinner clients and use
the database from a much larger number of clients.  A brief set of features
and how they can be used will be included below, with relavent sections of
the documentation.

I am assuming that you are writing about a new project that you will be
starting and have decided to use Perl and PostgreSQL for this project rather
than PHP/MySQL.  What I have done in cases like this is do cursory study of
the language involved (Perl) and other technologies and then get started.  I
have often had to disregard large sections of code due to bad design in the
past, but this is part of the learning experience, unfortunately.  At least
you can find good Perl tutorials on the web.
> I'll be discarding all database data for other reason anyway, so moving
> data isn't an issue. I just want to learn how to best use some of
> PostgreSQL's cool features. Is there any documentation about that? I've
> looked at the official docs, but they're very dry and don't talk much
> about migrating from MySQL. Any suggestions?

The cool features that we are talking about are not available in MySQL, so
there is no migration to be done.  I will include with the features a quick
description and section numbers of relavent docs (so that you can find them
on or offline).

1)  Subselects allow you to break up a complicated join into a smaller, more
readible query.  These allow your code to be easier to maintain.
Documentation is available in sections:
The SQL Language, section 4.2.9. Scaler Subqueries (read the intro to 4.2
for more info as well).
Also see the select entry in the SQL comamnd section of the Reference.

2:  Views allow you to present information to an application in ways other
than it is literally stored in the tables.  This can be used to partition
tables, provide limited access to portions of the table, and other tricks.
See the following sections of the documentation:
Tutorial , section 3.2: Views
Reference, SQL commands, Create View

3:  Rules are used internally to implement views, and can also be used to
allow a view to be updateable, as well as handle inserts and updates.  Views
work by expanding the query dynamically and substituting parts of it with
another query.
See: Reference, SQL Commands, Create Rule

4:  User defined functions, or Stored proceedures are functions which are
stored in the database and can then be called from queries.  These can be
written in SQL or C, or if the appropriate languages are installed, Pl/Pgsql
(SQL with proceedural extensions), Pl/Perl (Perl running inside the database
process), Pl/TCL, Pl/PHP, Pl/Python and many others.  These functions can
then form the basis for triggers and rules.
See:  Server Programming, Section 33 (lots of material here)
Also reference, SQL language, Create Function page.

5:  Triggers are similar in usefulness to rules with a notable exception:
rules are macros which are expanded when triggered by a query to a database
table of view, while triggers allow for functions to be called when a
database row is subject to an insert, update, or delete statement.  Triggers
are divided into two categories-- those that activate before the
insert/update/delete statements (and hence can alter the data of the row
subject to the insert/update, but not delete) and those triggered after.  In
each category, triggers are fired in alphabetical order.  Currently,
PostgreSQL will only trigger user-defined functions written in trigger-safe
proceedural languages, such as PL/PGSQL or C.  Triggers are used internally
to impliment foreign key constraints.
See documentation for number 3 above and
Reference/SQL Language/Create Trigger

6:  Schemas:  MySQL treats databases in a similar manner to schemas in
PostgreSQL.  Schemas are logical groups of tables which can be maintained in
relative isolation to eachother.  Tables in one schema can be the basis for
views in another.  This allows, for example, different applications to have
their own table structure without the actual storage changing.
See Reference/SQL Language/Schemas.

7:  information_schema (added in 7.4) allows client applications to
determine quite a bit of information about the current user, database,
schemas, etc. using standard select statements against database tables and
views in the information_schema.  See client interfaces, section 32.

8:  Domains allow an administrator to manage sets of similar data centrally.
For example, imagine we have 10 fields in the database where a telephone
number is stored.  We can create a domain and use it to manage the other 10
fields more appropriately.

9:  Inheritance allows tables to inherit characteristics of other abstract
tables.  I have not yet found much use for it but perhaps others can provide
insight.  Currently uniqueness of primary keys are not enforced across the
entire inheritance tree due to indexing issues.  Use at your own risk.

Since you are looking at using Perl, I won't get into extensible types,
except to note that others may be able to actually create additional data
types for you to use, in addition to the rich data types mentioned in SQL
Language, section 8.

Hope this helps.

Best Wishes,
Chris Travers