Versions Compared

Old Version 17

changes.mady.by.user Antony Scott (Deactivated)

Saved on

compared with

New Version Current

changes.mady.by.user Antony Scott (Deactivated)

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Input / Outputs

 Information

Visit the PostgreSQL documentation for a more in depth guide to PSQL.

Transactions

Database administration often involves providing a coordinated set of commands to the database. An important strength to PostgreSQL is the transaction system, which is where most actions can be executed within a transaction. This allows the administrator to build a script that will either all succeed or all fail, which can be critically important on a production system.

A transaction wraps up a string of commands. Use the word BEGIN to start a transaction and then at the end of transaction include the COMMIT; to complete the transaction.

The transaction will only succeed if all four commands succeed.

To test a transaction before committing is to use ROLLBACK. Rollback enables you to run the transaction to see if it is successful but without making and changes to the database. Rollback will return the data to its original state.

The transaction may succeed but no changes are made. The whole script will fail if at any point, one of the commands gives an error or higher message. While a transaction is in operation it has hold of table locks so other uses cannot modify the table.

- see the PostgreSQL documentation full details.

Functions

PostgreSQL functions, also known as Stored Procedures, allow you to carry out operations that would normally take several queries and round trips in a single function within the database. Functions allow database reuse as other applications can interact directly with your stored procedures instead of a middle-tier or duplicating code.There are package queries so that they can be called when needed by any database user, with parameters if appropriate. Postgres/PostGIS are installed with a large number of built in -build function in PostgreSQL and PostGIS. You functions - you will find them in the public schema.

Functions can be created in the language of your choice like , including SQL, PL/pgSQL, C, Python, etc.

N.B. PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu.

The and Python - the most common language for creating function is PL/pgSQL.

Here is the The basic syntax of a function is below:

Here is a very simple example:

Although function names do not need to be schema qualified, it is recommended that they are placed in a schema. If no schema is specified they

Numberedheadings
number-formatdecimal
skip-headingsHNaN
start-numbering-with105
h1[h1.decimal].
h2[h1.decimal].[h2.upper-latin]
h3
h4
h5
enabledtrue
h6
start-numbering-atH1

Automating SQL Tasks 

psql

The course has focused on carrying out database tasks in pgAdmin with SQL, and in QGIS. However full database functionality is also available through command line shell applications, where an experienced database user has full and better control over the database. Included in pgAdmin is the command line application Tools > PSQL Tool. In a normal PostgreSQL installation it is There will be times when you need a task in SQL to be automated, so that it can be triggered from another application, or scheduled to run at a specific time. This module describes a few options for doing this.

psql

Full database functionality is available through the psql command line shell application. This can be accessed from within pgAdmin, or on the command line - it is installed auomatically with Postgres, normally in the bin folder (e.g. C:\Program Files\PostgreSQL\13\bin\pgsql.exe), and so can be called from a batch file.

Full documentation on psql commands can be found here.Using

psql

from pgAdmin means that your database connection is already established. If you run pgsql from a command prompt, you can

in pgAdmin

  1. Got to Tools > PSQL Tool to start psql - you will see a screen like this, already connected to your database

  2. Now type in an SQL command into the prompt, remembering to close the statement with a semicolon, for example:
    select * from geometry_columns;

  3. The full table will appear in the console

Run pqsl from the Command Prompt

You can run pgsql directly from a command prompt (in AppStream, use the OSGeo4W Shell command from the menu).

  1. Start psql by typing the following, which includes the parameters for the database connection (use your own database name)
    psql -h appstream_data.astuntechnology.com -d chris_guest3 -p 5432 -U postgis

  2. Enter your password (postgis) at the prompt - you will see the following:

  3. You can now enter an SQL statement in the same way as you did in pgAdmin, and press Enter to run it - try this with a SELECT statement on one of your tables, and don’t forget to add a semi-colon ';' at the end

If you are working from the command line, and always using the same database, set the environment variables for the database connection to avoid having use the parameters each time - for example:

Alternatively, connect as in the following example (you will be prompted for a password):

Running SQL in psql

To run an SQL command, use the -c option e.g.:
psql -c "select count(*) from geometry_columns"

To execute a script, use the -f option:
psql -f script/vml_count_features.sql

set PGHOST=appstream_data.astuntechnology.com
set PGUSER=postgis
set PGPASSWORD=ppostgis
set PGDATABASE=chris_guest3
set PGPORT=5432

psql Meta-commands

As well as being able to run SQL, psql includes a set of meta-commands, prefixed with a backslash, that are processed by psql itself. Meta-commands make psql useful for administration or scripting .

Basics

\h

get help with SQL

\?

get help with PSQL

\g OR ;

execute a query

\q

quit

\cd

change directory

\echo [STRING]

write string to standard output

\i [FILE]

execute commands from file

\o [FILE]

send all query results to file

\d [OBJECTNAME]

describes tables, sequences views etc.

\db

lists tablespaces

\df

lists functions

\dn

lists schemas

\dp OR \z

lists tables, views sequences etc.

\du

lists users

\l

lists all databases

\dt

lists tables

CREATE [OR REPLACE] FUNCTION function_name (arguments)
RETURNS return_datatype AS
$block_name$
DECLARE
declaration;
[...]
BEGIN
< function_body >
[...]
RETURN { variable_name | value }
END;
$block_name$
LANGUAGE plpgsql;

  1. Create a simple function below by running the code below in the Query Tool
    CREATE OR REPLACE FUNCTION general.myfunction(a integer, b integer)
    RETURNS integer AS
    $$
    BEGIN
    return a*b;
    END;
    $$
    LANGUAGE plpgsql;

  2. Note that a schema name is not required - if it is not supplied, the function will be created in the public schema

  3. Call the function using a SELECT statement in the Query Tool
    select general.

To run the above function you would simply call the function via a select clause. e.g.

  1. myfunction(4,3)

  2. If you have time, make a change to the function and call it again - note that to update the function, you can select it in pgAdmin then right-click > Scripts > CREATE Script, then amend the function definition run the statement

For more details on function functions see https://www.postgresql.org/docs/9.4/static/sql-createfunction.htmlFor more , and for details on PL/pgSQL see https://www.postgresql.org/docs/9.4/static/plpgsql.html

Python Functions

You can write functions in python (and many other languages), first create the plpython3u extension using CREATE EXTENSION IF NOT EXISTS plpython3u . Then you can create a function using code like:

The lines above the first $$ define the function and it's input and output parameters, then the code between the two $$ marks is a string containing the python code.

First we import a module (xlrd) that handles reading .xls files from Excel. Then we open the workbook contained in the file who's name was passed in, and find the first sheet in the workbook. Finally, we loop through the rows (after skipping row 0 - the header) and return (or yield) the value of the first three cells in the current row. Using yield rather than return means our function can keep track of which row it's on.

The PL/Python extension also provides a python module plpy that provides access to the database. This allows you to query the catalog tables and run {{UPDATE}}s on tables based on the answers.

Triggers

A trigger is a set of actions that are run automatically when a specified change operation (SQL INSERT, UPDATE, DELETE or TRUNCATE statement) is performed on a specified table. Triggers are useful for tasks such as enforcing business rules, validating input data, and keeping an audit trail.

  1. Create

trigger

A trigger is a named database object that is associated with a table, and it activates when a particular event (e.g. an insert, update or delete) occurs for the table/views. The statement CREATE TRIGGER creates a new trigger in PostgreSQL. Here is the syntax :

Syntax

Parameters

Name

Description

name

The name of the trigger. A trigger must be distinct from the name of any other trigger for the same table. The name cannot be schema-qualified — the trigger inherits the schema of its table. 

BEFORE 
AFTER
INSTEAD OF

Determines whether the function is called before, after, or instead of the event. A constraint trigger can only be specified as AFTER.

event

One of INSERT, UPDATE, DELETE, or TRUNCATE, that will fire the trigger.

table_name

The name of the table or view the trigger is for.

referenced_table_name

The (possibly schema-qualified) name of another table referenced by the constraint. This option is used for foreign-key constraints and is not recommended for general use. This can only be specified for constraint triggers.

DEFERRABLE NOT 
DEFERRABLE 
INITIALLY IMMEDIATE 
INITIALLY DEFERRED

The default timing of the trigger.

FOR EACH ROW 
FOR EACH STATEMENT

Specifies whether the trigger procedure should be fired once for every row affected by the trigger event, or just once per SQL statement. If neither is specified, FOR EACH STATEMENT is the default.

condition

A Boolean expression that determines whether the trigger function will actually be executed.

function_name

A user-supplied function that is declared as taking no arguments and returning type trigger, which is executed when the trigger fires.

arguments

An optional comma-separated list of arguments to be provided to the function when the trigger is executed. The arguments are literal string constants.

Triggers that are specified to fire INSTEAD OF the trigger event must be marked FOR EACH ROW, and can only be defined on views. BEFORE and AFTER triggers on a view must be marked as FOR EACH STATEMENT. In addition, triggers may be defined to fire for TRUNCATE, though only FOR EACH STATEMENT. The following table summarizes which types of triggers may be used on tables and views:

When

Event

Row-level

Statement-level

BEFORE

INSERT/UPDATE/DELETE

Tables

Tables and views

TRUNCATE

—

Tables

AFTER

INSERT/UPDATE/DELETE

Tables

Tables and views

TRUNCATE

—

Tables

INSTEAD OF

INSERT/UPDATE/DELETE

Views

—

TRUNCATE

—

—

Here is a simple example of trigger function.:

Now we can

  1. a test table by running the SQL below
    CREATE TABLE test_table( col1 text, col2 text, col3 date);

  2. Create a trigger function which sets col3 to today’s date when the trigger is executed - this is the action the trigger will generated
    CREATE OR REPLACE FUNCTION test_table_trig() RETURNS TRIGGER as
    $$
    BEGIN
    new.col3 = current_date;
    return new;
    END;
    $$
    LANGUAGE plpgsql;

  3. Now create the trigger which will fire at the time of execution

; the event as specified in the trigger for the associated tables.

In the above trigger function there is new keyword 'NEW' which is a PostgreSQL extension to triggers. There are two PostgreSQL extensions to trigger 'OLD' and 'NEW'. OLD and NEW are not case sensitive.

  • Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger

  • In an INSERT trigger, only NEW.col_name can be used.

  • In a UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.

  • In a DELETE trigger, only OLD.col_name can be used; there is no new row.

A column named with OLD is read only. You can refer to it (if you have the SELECT privilege), but not modify it. You can refer to a column named with NEW if you have the SELECT privilege for it. In a BEFORE trigger, you can also change its value with SET NEW.col_name = value if you have the UPDATE privilege for it. This means you can use a trigger to modify the values to be inserted into a new row or used to update a row. (Such a SET statement has no effect in an AFTER trigger because the row change will have already occurred.)

Here is another example of a trigger, which writes to an audit table.


  1. CREATE TRIGGER test_table_trigger
    BEFORE INSERT
    ON test_table
    FOR EACH ROW
    EXECUTE PROCEDURE test_table_trig();

  2. Test the trigger by using INSERT to add a row to the table
    INSERT INTO test_table(col1,col2) VALUES('1','2')

  3. Look at the table data, and check that today’s date has been added as a value for column 3

For more information on triggers and trigger functions see https://www.postgresql.org/docs/9.4/static/plpgsql-trigger.html .

Python

To use PostGIS from a Python application you need the Psycopg adapter so you can access PostgreSQL from Python.

Psycopg

Psycopg is the most popular PostgreSQL database adapter for the Python programming language. Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety (several threads can share the same connection). It was designed for heavily multi-threaded applications that create and destroy lots of cursors and make a large number of concurrent INSERTs or UPDATEs.

Psycopg 2 is mostly implemented in C as a libpq wrapper, resulting in being both efficient and secure. It features client-side and server-side cursors, asynchronous communication and notifications, COPY TO/COPY FROM support. Many Python types are supported out-of-the-box and adapted to matching PostgreSQL data types; adaptation can be extended and customized thanks to a flexible objects adaptation system.

Psycopg 2 is both Unicode and Python 3 friendly.

On Windows machines use the following to install psycopg2.

Very simple examples of using Psycopg2

What about geometry?

For a simply point you can easily use the appropriate PostGIS functions e.g.

For more complicated geometries, such as LineString and Polygon geometries, you can handle them with a number of tools including

Useful references:

Python Geospatial Development, Erik Westra

Geoprocessing with Python, Chris Garrard

Simple example using Shapely

Shapely does manipulating and analyzing data. It’s based on GEOS, the libraries used by PostGIS. With Shapely, you can do things like buffers, unions, intersections, centroids, convex hulls.

Shapely, then passes them through psycopg2 as hex-encoded WKB. Note that Shapely 1.3 or later is required to handle the export of 3D geometries with the wkb_hex property.

Of course this can be accomplished by sending the geometry's WKT, however since it is converted to text, it is lossy and may reduce angstroms of precision. Transferring geometries as hex-encoded WKB is lossless, and preserves the exact precision of each coordinate.

QGIS

Because QGIS can be used to run SQL on a Postgres database, and it also has a workflow tool (the Graphical Modeller), it can be used to automate tasks in Postgres.

  1. Open QGIS, and go to Processing > Graphical Modeler

  2. In the Algorithms panel, search for Postgres, and double-click on PostgreSQL Execute SQL to include the process in your model

  3. Double-click on the process in the model window to open it

  4. In the dialog:

    1. Set the Description to Add column

    2. Set the Database (connection name) to the connection you created earlier

    3. In the SQL query field, add
      ALTER TABLE test_table ADD COLUMN testedit_date date

    4. Click OK to save these settings

  5. In the Model Designer, click the Run model button or F5 to run the model

  6. In pgAdmin, check that the new field has been added to the data, and right-click > Delete/Drop to remove it so we can run the process again

  7. In the Graphical Modeler, add another PostgreSQL Execute SQL process to the model in the same way as before

  8. This time, rename the process Add values, and add some data to the table you have just updated, using the following SQL in the SQL query field
    INSERT INTO test_table(col1,col2, test_from_qgis) VALUES('3','4','test_value')

  9. Under Dependencies, add a dependency on the previous process - this will make this process run after the previous one

  10. Click OK - your model should look something like this

  11. Run the model, then check the results in pgAdmin

  12. When you are finished, save the model for future use