Oracle Tips by
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.
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
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.
5.43: Developer Studio Function Icons
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.
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
Compilation is achieved in the same way as SPL. Enter your code
in the SQL Interactive window and hit the running man to compile.
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.
the easiest route is to use the routines catalog table in the ANSI
information schema catalog.
SELECT routine_name, routine_definition
WHERE external_language IN ('PLPGSQL', 'PLPERL', 'PLPERLU', 'PLTCL', 'PLTCLU');
returns the values in Figure 5.44:
5.44: Non-SPL Function List
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):
5.45: Display Printable Code
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.
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
Developer Studio backup is comparable to an Oracle Export. The
Developer Studio restore works much like the Oracle Import.
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).
that this is a database level backup, not a cluster backup. To
backup a cluster, you must backup each database in the cluster.
5.46: Backup Options
options on the backup screen are:
– You may choose a single table or all tables. Code will also be
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
Verbose – The verbose mode displays each object that has been backed
up in the dialog following this one.
To File – This is the location, including path, for the dump file.
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.
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).
5.48: Restore Dialog
press the Ok button, you will get again get a pg_dump dialog (Figure
5.49). This time it will display all objects restored.
5.49: pg_dump Restore Output Dialog
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.
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress.