 |
|
EnterpriseDB: PL/pgSQL
Oracle Tips by
Burleson
|
Support
for PL/pgSQL is fairly robust. All PL/pgSQL stored procedures
are functions. That means that only SPL allows you to create
procedures and packages.
To
compile a PL/pgSQL function, enter the code in the SQL Interactive
window and hit the running man icon (as you would for a SPL
procedure).
In the
navigator, under the functions node (Figure 5.43), you can get a list
of all available functions. EnterpriseDB SPL functions have a
shark's head icon next to them and PL/pgSQL functions have an
elephant's head next to them.
Figure
5.43: Developer Studio Function Icons
You will
use the SQL Interactive window to develop PL/pgSQL code just as you do
with SPL. You must follow naming and coding requirements as
defined for PostgreSQL.
Other Languages
At this
time, Developer Studio does not provide robust support for the
additional languages that are standard with PostgreSQL distributions
(such as PL/TCL and PL/Perl). You may compile them using
Developer Studio, but they do not show up in the Developer Studio
navigator.
Compilation is achieved in the same way as SPL. Enter your code
in the SQL Interactive window and hit the running man to compile.
The
easiest way to see these other language code routines is to use a tool
that supports them (which I speak of below). You may also select
this information from several system catalog tables.
I find
the easiest route is to use the routines catalog table in the ANSI
information schema catalog.
SELECT routine_name, routine_definition
FROM information_schema.routines
WHERE external_language IN ('PLPGSQL', 'PLPERL', 'PLPERLU', 'PLTCL', 'PLTCLU');
This
returns the values in Figure 5.44:
Figure
5.44: Non-SPL Function List
If you
double-click on the routine_definition column of one of the rows, you
can get an indented, printable view of the function (Figure 5.45):
Figure
5.45: Display Printable Code
You can
also export this code to a text file via the Use File and Export
button. Enter the file name (including directories and drives)
and press Export. You can view and save any code in this manner,
including SPL code.
To close
this window, hit the X in the upper right hand corner.
Backup and Restore
Developer Studio provides a method for backing up databases (including
DDL and data or just data). For large databases, you will want
to use an OS level backup tool or run scheduled backups (I cover
scheduling in a later chapter). However, for most databases
under a few gigabytes, the Developer Studio backup and restore tool is
sufficient.
A
Developer Studio backup is comparable to an Oracle Export. The
Developer Studio restore works much like the Oracle Import.
To
backup a database, choose the database you wish to backup in the
Navigator and select backup from the Tools menu. This will
launch the Backup Database dialog (Figure 5.46).
Remember
that this is a database level backup, not a cluster backup. To
backup a cluster, you must backup each database in the cluster.
Figure
5.46: Backup Options
The
options on the backup screen are:
* Table
– You may choose a single table or all tables. Code will also be
backed up.
* Dump
Method – The copy method seems to be the most performant to me.
Copy copies all values in a loadable format. The other two
options include Insert statements with and without column lists.
Using Insert with column lists is the most portable but creates the
largest export file.
*
Content Selectivity – This determines how much of the database to
backup. Choosing Only Data dumps data only, choosing Only Schema
dumps all of the DDL. Choosing Data and Schema takes a full
database snapshot.
*
Verbose – The verbose mode displays each object that has been backed
up in the dialog following this one.
* Dump
To File – This is the location, including path, for the dump file.
Execute
the backup by pressing Ok. While the backup is running, you will
get a pg_dump output dialog (Figure 5.47). This screen will also
provide a result code at completion.
Figure
5.47: pg_dump Backup Output Dialog
Restoring a database is just as easy as backing one up.
Choose Restore from the Tools menu and enter the file name to restore
from on the Restore dialog (Figure 5.48).
Figure
5.48: Restore Dialog
When you
press the Ok button, you will get again get a pg_dump dialog (Figure
5.49). This time it will display all objects restored.
Figure
5.49: pg_dump Restore Output Dialog
I would
recommend that you keep scripts for all of your code and DDL and just
use backup and restore for data. The main reason for this is
that you can have much granular control over your database objects and
you can check each object into a version control system.
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.
|
