PostgreSql Four - Plsql 4 (40 Min)

February 01, 2022

PostgreSql Four - Plsql 4 (40 Min)

Temp Table

Type 1

- We are creating a temp table which will be deleted on the commit execution

- It exists only on the current window.

- it does not have physical memory

CREATE TEMP TABLE temp_cities

(

name VARCHAR(80)

)

ON COMMIT DELETE ROWS;

BEGIN TRANSACTION;

INSERT INTO temp_cities VALUES ('Warsaw');

INSERT INTO temp_cities VALUES ('Prague');

INSERT INTO temp_cities VALUES ('Milan');

SELECT COUNT(*) FROM temp_cities;

-- Result: 3

COMMIT;

SELECT COUNT(*) FROM temp_cities;

--------------------------------------------------

Type 2

- It exists only on the current window.

- it does not have physical memory

CREATE TEMP TABLE temps as select * from actor;

select * from temps;

drop table if exists temps

Global Temp Table

What is the difference between clauses 'temp table' vs 'global temporary table' in postgresql?

There is no difference in Postgres. As the documentation explains:

The SQL standard also distinguishes between global and local temporary tables, where a local temporary table has a separate set of contents for each SQL module within each session, though its definition is still shared across sessions. Since PostgreSQL does not support SQL modules, this distinction is not relevant in PostgreSQL.

For compatibility's sake, PostgreSQL will accept the GLOBAL and LOCAL keywords in a temporary table declaration, but they currently have no effect. Use of these keywords is discouraged, since future versions of PostgreSQL might adopt a more standard-compliant interpretation of their meaning.

--------------------------------------------------

Xml usage

xml in function

create temp table t(x xml) on commit drop;

insert into t values('<?xml version="1.0" encoding="UTF-8"?>

<E-Document>

</BuyerParty>

</DocumentParties>

</DocumentInfo>

</ItemReserveUnit>

</ItemReserve>

</ItemEntry>

</ItemReserveUnit>

</ItemReserve>

</ItemEntry>

</DocumentItem>

</Document>

</E-Document>

'::xml);

SELECT

unnest(xpath('/E-Document/Document/DocumentInfo/DocumentNum/text()',

x))::text AS docnumber,

unnest( xpath(

'/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',

x))::text AS ContactFirstName,

unnest(xpath('/E-Document/Document/DocumentItem/ItemEntry/SellerItemCode/text()',

x))::text AS itemcode

FROM t

---

create table based on xml

create temp table t(x xml) on commit drop;

insert into t values('<?xml version="1.0" encoding="UTF-8"?>

<E-Document>

</BuyerParty>

</DocumentParties>

</DocumentInfo>

</ItemReserveUnit>

</ItemReserve>

</ItemEntry>

</ItemReserveUnit>

</ItemReserve>

</ItemEntry>

</DocumentItem>

</Document>

</E-Document>

'::xml);

create table xmltest as

SELECT

unnest(xpath('/E-Document/Document/DocumentInfo/DocumentNum/text()',

x))::text AS docnumber,

unnest( xpath(

'/E-Document/Document/DocumentParties/BuyerParty/ContactData/ContactFirstName/text()',

x))::text AS ContactFirstName,

unnest(xpath('/E-Document/Document/DocumentItem/ItemEntry/SellerItemCode/text()',

x))::text AS itemcode

FROM t

--------------------------------------------------

By the same way you can insert and update rows

CREATE or REPLACE FUNCTION

multiple_bin_update(rack_num xml,

bin_num text)

returns

setof refcursor

language

'plpgsql'

$BODY$

DECLARE

msg text := 'No Values';

BEGIN

update

wm_pckinfo

set c_bin=bin_num WHERE c_rackid IN (

SELECT unnest

(xpath('.//RackID/text()', rack_num::xml))::text AS XMLDATA);

msg := 'Updated';

RETURN NEXT msg;

END;

$BODY$;

--------------------------------------------------

Triggers

CREATE TABLE "Employee"

(

"EmployeeId" INT NOT NULL,

"LastName" VARCHAR(20) NOT NULL,

"FirstName" VARCHAR(20) NOT NULL,

"Title" VARCHAR(30),

"ReportsTo" INT,

"BirthDate" TIMESTAMP,

"HireDate" TIMESTAMP,

"Address" VARCHAR(70),

"City" VARCHAR(40),

"State" VARCHAR(40),

"Country" VARCHAR(40),

"PostalCode" VARCHAR(10),

"Phone" VARCHAR(24),

"Fax" VARCHAR(24),

"Email" VARCHAR(60),

CONSTRAINT "PK_Employee" PRIMARY KEY ("EmployeeId")

);

CREATE TABLE "Employee_Audit"

(

"EmployeeId" INT NOT NULL,

"LastName" VARCHAR(20) NOT NULL,

"FirstName" VARCHAR(20) NOT NULL,

"UserName" VARCHAR(20) NOT NULL,

"EmpAdditionTime" VARCHAR(20) NOT NULL

);

CREATE OR REPLACE FUNCTION employee_insert_trigger_fnc()

RETURNS trigger AS

BEGIN

INSERT INTO "Employee_Audit" ( "EmployeeId", "LastName", "FirstName","UserName" ,"EmpAdditionTime")

VALUES(NEW."EmployeeId",NEW."LastName",NEW."FirstName",current_user,current_date);

RETURN NEW;

END;

LANGUAGE 'plpgsql';

CREATE TRIGGER employee_insert_trigger

AFTER INSERT

ON "Employee"

FOR EACH ROW

EXECUTE PROCEDURE employee_insert_trigger_fnc();

INSERT INTO "Employee" VALUES(10,' Adams','Andrew','Manager',1,'1962-02-18 00:00:00','2010-08-14 00:00:00','11120 Jasper Ave NW','Edmonton','AB','Canada','T5K 2N1','+1 780 428-9482','+1 780 428-3457','abc@gmail.com');

select * from "Employee" where "EmployeeId" =10;

select * from "Employee_Audit" ;

----------------------------

Uses Of Triggers

1. Auditing: You can use triggers to track the table transactions by logging the event details.

2. Forcing Check Constraint: You can create a trigger by which you can check the constraints before applying the transaction to the table.

3. Automatic Population: By using triggers you can also auto populate tables fields by new transactions records manipulation.

Important Points To Remember

1. To create a trigger on a table, the user must have the TRIGGER privilege on the table and EXECUTE privilege on the trigger function.

2. You can check system catalogue “pg_trigger” for the existing trigger information in the database.

3. If you create multiple triggers on the same object for the same event, those triggers will be fired in alphabetical order by name.

----------------------------

Index

The following query finds the address whose phone number is 223664661973:

SELECT * FROM address

WHERE phone = '223664661973';

Code language: JavaScript (javascript)

It is obvious that the database engine had to scan the whole address table to look for the address because there is no index available for the phone column.

To show the query plan, you use the EXPLAIN statement as follows:

EXPLAIN SELECT *

FROM address

WHERE phone = '223664661973';

Code language: JavaScript (javascript)

Here is the output:

PostgreSQL CREATE INDEX - sequential scan example

To create an index for the values in the phone column of the address table, you use the following statement:

CREATE INDEX idx_address_phone

ON address(phone);

Now, if you execute the query again, you will find that the database engine uses the index for lookup:

EXPLAIN SELECT *

FROM address

WHERE phone = '223664661973';

----------------------------

Tuning

- avoid duplicate query

- select 1 instead of select * in if condition

- use index in large table

- use normalization

- use function if necessary

- use cte instead of using same query two times in a window

- avoid temp table as much as possible it's consume more memory

- use Table Partitioning

- explain query (exc plan) helps to tune performance

----------------------------

Explain plan

The EXPLAIN statement returns the execution plan which PostgreSQL planner generates for a given statement.

The EXPLAIN shows how tables involved in a statement will be scanned by index scan or sequential scan, etc., and if multiple tables are used, what kind of join algorithm will be used.

The most important and useful information that the EXPLAIN statement returns are start-cost before the first row can be returned and the total cost to return the complete result set.

CREATE TABLE authors (

id int4 PRIMARY KEY,

name varchar

);

CREATE TABLE books (

id int4 PRIMARY KEY,

author_id int4,

title varchar

);

If we use the query:

EXPLAIN ANALYZE SELECT authors.name, books.title

FROM books, authors

WHERE books.author_id=16 and authors.id = books.author_id

ORDER BY books.title;

--------------------------------------------------

Search This Blog

Tech Conscious

PostgreSql Four - Plsql 4 (40 Min)

Comments

Post a Comment

Popular Posts

How to use PostgreSql With .Net

SQL Interview Questions