PL/pgSQL - An introduction on how to use imperative programming in postgresql

50 %
50 %
Information about PL/pgSQL - An introduction on how to use imperative programming in...
Technology

Published on March 20, 2014

Author: robertsosinski

Source: slideshare.net

Description

When you start tackling complex high-volume data problems, SQL might not be enough. When your logic needs to be close to your data, Postgres has a secret weapon, PL/PGSQL.

An imperative language that extends SQL, PL/PGSQL lets you execute complex read and write logic directly on the database. The benefits include increased speed and concurrency, ability to share logic among many applications, strong consistency guarantees, and greater query flexibility.

Robert Sosinski, Founder and Engineering Fellow at Reactive.IO will demonstrates what PL/PGSQL is as a language and provides examples where it can benefit you as a developer.

He will then show you how PL/PGSQL functions can speed up your indexes, accelerate complex queries, activate custom triggers on data changes, and handle complex write situations. You will then be able to take control of your data directly at the source, PostgreSQL itself.

Key points discussed:

* PL/pgSQL: what it is and why it matters
* Volatility: living in an uncertain data world
* Upserts: a little update with a little insert
* Triggers: keeping data consistent
* Code Blocks: control when you need it

SQL examples given during the presentation are available here: http://www.reactive.io/downloads/presentations/postgresql/plpgsql/plpgsql-examples.zip

PL/PGSQL AN INTRODUCTION ON HOW TO USE IMPERATIVE PROGRAMMING IN POSTGRESQL

Robert Sosinski Founder & Engineering Fellow

AGENDA PL/pgSQL: what it is and why it matters Volatility: living in an uncertain data world Upserts: a little update with a little insert Triggers: keeping data consistent Code Blocks: control when you need it Summary: bringing it all together Questions: fire away

WHAT IS PL/PGSQL Procedural Language postgres Structured Query Language

HOW PL MEETS SQL PL/pgSQL SQL

WHY SQL IS NOT ENOUGH With SQL, every statement must be executed individually by the database server 1. Complex actions can require multiple round-trips 2. Intermediate results have to be marshaled and transferred 3. Multiple clients must implement redundant logic 4. Secure information might require exposure 5. Indexes and common filters might need complex logic 6. Consistency can require client-side interaction

TWO WAYS TO PL/PGSQL Stored Procedure • Stored directly on the database server • Can be used in queries and indexes • Able to be activated upon trigger conditions • May take input parameters and return values Anonymous Code Block • Sent to and parsed by the database when needed • Able to write to the database but unable to return a value

MORE PL THEN JUST PGSQL PL/Perl PL/TCL PL/Python PL/Ruby PL/PHPPL/V8PL/Java

DIVING HEAD FIRST Secure access database for a fictitious company with high performance needs.

LETS WRITE SOME FUNCTIONS Open Postgres Terminal

DEALING WITH VOLATILITY Volatility Categories provide assumptions the the query planner on how to optimize a function call

IMMUTABLE Characteristics • Cannot modify the database • Should not read from the database (side effect free) • Similar inputs should always return the same output • Can be used when creating indexes • Database may replace a function call with a constant Examples • Our check_access_for_user function for user types • The lower function for string types

STABLE Characteristics • Cannot modify the database • Able to read from database • Similar inputs should always return the same output on a per statement basis • Can be used in index conditions, but not creation • Can give caching benefits when called multiple times Examples • The current_timestamp and now function

VOLATILE Characteristics • Default volatility classification if none is specified • Can read from and write to the database • Can return different data on each call • Can be used as trigger functions • Planner will not make any assumptions for optimization Examples • The random and timeofday functions • Sequence functions such as nextval and currval

LETS TRY TO WRITE SOME STABLE FUNCTIONS Open Postgres Terminal

PULLING THE TRIGGER Trigger Function • The function to be run when a triggered event happens • Written using PL/pgSQL or any other PL language • Has special arguments available for control flow • If returns NULL, the event is canceled, else it continues Trigger Definition • The event that causes the trigger to be run • Always attached directly to the table or view • Can be run BEFORE, AFTER or INSTEAD OF • Can be run run for EACH ROW or EACH STATEMENT

LETS PULL SOME TRIGGERS Open Postgres Terminal

ANONYMOUS CODE BLOCKS The Good • You do not have to store the function on the server • Can run arbitrary PL/pgSQL when it is needed • Good for admin functions stored in your application The Bad • Cannot take arguments, so must be injected by your app • Unable to return a value, but you can get feedback • Must be parsed each time they are used

LETS “DO” SOMETHING Open Postgres Terminal

SUMMARY Powerful: have direct imperative access to your data Easy: syntax is familiar and simple to use Fast: because you are never leaving the database Secure: you do not need to send data back and forth Flexible: store the logic on the database or in your app

CONDENSED SUMMARY Direct access to your data means better control of your business

THANKS Open For Questions

Add a comment

Related presentations

Presentación que realice en el Evento Nacional de Gobierno Abierto, realizado los ...

In this presentation we will describe our experience developing with a highly dyna...

Presentation to the LITA Forum 7th November 2014 Albuquerque, NM

Un recorrido por los cambios que nos generará el wearabletech en el futuro

Um paralelo entre as novidades & mercado em Wearable Computing e Tecnologias Assis...

Microsoft finally joins the smartwatch and fitness tracker game by introducing the...

Related pages

PL/PGSQL - Reactive.IO

AN INTRODUCTION ON USING IMPERATIVE PROGRAMMING IN POSTGRESQL . Robert Sosinski Founder & Engineering Fellow . AGENDA PL/pgSQL: ... familiar and simple to use
Read more

SQL - Wikipedia

... makes use of the declarative ... not an imperative programming language ... PL/pgSQL: Procedural Language/PostgreSQL Structured ...
Read more

Domain specific languages with SQL using table oriented ...

... pl/pgsql which come with various databases make imperative programing possible. ... i will only use the basic features of PostgreSQL in this article.
Read more

218Part3 - PostgreSQL PL/PSM Procedural Language ...

PostgreSQL PL/PSM Procedural Language/Persistent Stored Modules ... PostgreSQL PL/pgSQL Procedural Language/PostgreSQL ... INTRODUCTION TO SQL SQL ...
Read more

PostgreSQL - revolvy.com

PostgreSQL is developed by the PostgreSQL ... however the PostgreSQL Core Team announced in 2007 that the product would continue to use the n Express ...
Read more

An introduction to the imperative in French - Education

Download An introduction to the imperative in French. Transcript. 1. L’Impératif. 2. L ... utiliser to use Utilise Utilisez. 8. manger to eat Mange Mangez.
Read more

Postgres – Java, SQL and jOOQ.

... PL/Java PL/pgSQL PL/SQL Postgres ... imperative programming is quite ... com/introduction-pl-java. In essence, you can use another ...
Read more

Programming and SQL Edel Sherratt. Motivation 1: Integrity ...

Programming and SQL Edel Sherratt. Motivation 1: Integrity Checking Sometimes primary keys and foreign keys are not enough For example, they do not enforce.
Read more