PostgreSQL - Funcții

La fel ca în majoritatea bazelor de date, putem înlănțui o serie de propoziții SQL și tratați-le ca o unitate a unui bloc funcțional; diferitele baze de date descriu această operațiune cu nume diferite, proceduri stocate, module, macrocomenzi etc.

În PostgreSQL se numesc funcții. În afară de unificarea mai multor instrucțiuni SQL, aceste funcții ne oferă și posibilitatea de a executa instrucțiuni SQL folosind limbaje procedurale (PL).

În PostgreSQL avem mai multe opțiuni pentru acest lucru și posibilitatea de a le extinde.

1. Funcții PostgreSQL

Anatomia unei funcții
Indiferent de limbajul folosit pentru a scrie funcțiile, acestea au o structură, această structură poate fi sintetizată cu următoarele:

 CREAȚI SAU ÎNLOCUIȚI FUNCȚIA func_name (arg1_arg1datatype) RETURNĂ some_type / setoff sometype / TABLE / (…) / AS $$ BODY off function $$ LIMBA language_of_function

Dacă descriem ceea ce vedem, este destul de simplu, CREAȚI SAU ÎNLOCUIȚI FUNCȚIA este clauza de creare a funcției, func_name este numele pe care îl va avea arg1 este parametrul pe care îl va primi și arg1_datatype este tipul de date care este respectivul parametru, adică dacă este un număr întreg, un șir etc. În SE INTOARCE Întoarcem rezultatul funcției noastre, $$ este începutul blocului care va da loc corpului funcției și apoi se termină la fel cu $$ și în cele din urmă LIMBA ne permite să specificăm limba în care este scrisă funcția.

Este o structură bine definită și destul de lizibilă, deci nu ar trebui să avem probleme la crearea funcțiilor noastre.

Scrierea funcțiilor cu SQL
Scrieți funcțiile folosind SQL Este destul de ușor și rapid, este să ne luăm practic instrucțiunile SQL și să adăugăm antetul și subsolul funcțiilor și am terminat.

Cu toate acestea, la fel ca orice altceva, acest lucru vine cu prețul unor sacrificii, de exemplu, pierdem flexibilitatea că, dacă am putea lucra cu un alt limbaj pentru a crea mai multe ramuri ale controlului condiționat al execuției, nu putem avea mai multe instrucțiuni SQL, deși acest lucru poate fi remediat. folosind diverse metode.

Cel mai mare avantaj este că, deoarece SQL este programatorul PostgreSQL ne permite să profităm de indici și astfel să accelerăm execuția acestuia, pe de altă parte cu alte limbi funcția va fi întotdeauna o cutie neagră

Acum să vedem o funcție scrisă cu SQL:

 CREAȚI SAU ÎNLOCUIȚI FUNCȚIA ins_logs (param_user_name varchar, param_description text) RETURNEȘTE întreg AS $$ INSERT INTO logs (user_name, description) VALORI ($ 1, $ 2) RETURNING log_id; $$ LIMBA 'sql' VOLATIL;

Vedem că urmărim structura definită mai sus și la sfârșit în secțiune LIMBA definim clauza „sql” VOLATIL Faptul că deține înseamnă că funcția poate returna ceva diferit cu fiecare apel efectuat, chiar dacă primește aceiași parametri. Apoi, pentru a apela funcția noastră, putem folosi:

 SELECT ins_logs ('lhsu', 'acesta este un test') Ca new_id;

Facem o propoziție SELECTAȚI, funcția este activă și ceea ce returnează este ceea ce vom primi și în acest caz vom vedea o valoare pe care o numim new_id și că funcția returnează ca log_id.
Putem chiar să folosim o funcție pentru a face o actualizare a înregistrării și a returna un parametru nul ca în acest exemplu:

 CREAȚI SAU ÎNLOCUIȚI FUNCȚIA upd_logs (log_id integer, param_user_name varchar, param_description text) RETURNS nul AS $$ ACTUALIZARE jurnale SET user_name = $ 2, description = $ 3, log_ts = CURRENT_TIMESTAMPWHERE log_id = $ 1; $$ LANGUILE 'sql' sql 'VL „VOLATIL”

Fiind nule, nu avem nevoie de un câmp receptor, așa că îl executăm după cum urmează:

 SELECT upd_logs (12, 'robe', 'Schimbați în regina');

Aici putem vedea că am eliminat ultimul pas din As new_id din apelul anterior.
Cu aceasta terminăm acest tutorial, acum ne putem îndeplini funcțiile de bază în SQL, facilitând astfel și simplificând multe activități de care s-ar putea să avem nevoie în cadrul unui program sau sistem pe care îl construim.

2. Funcțiile PostgreSQL în alte limbi

Una dintre cele mai atractive caracteristici ale PostgreSQL este că nu se limitează doar la limbajul SQLDatorită încărcării modulelor, putem alege să încorporăm funcționalități avansate, inclusiv posibilitatea de a utiliza diferite limbi pentru a construi funcții, cu aceasta putem obține o flexibilitate mare folosind capacități mai bune de generare a condiționarilor și avantajele inerente diferitelor limbi.

Funcții de scriere cu PL / pgSQL
În momentul în care observăm că standardul SQL este scurt pentru interogările pe care dorim să le realizăm într-o funcție, putem recurge oricând la utilizarea PL / pgSQL; una dintre diferențele și îmbunătățirile sale în raport cu SQL este că variabilele locale pot fi declarate folosind instrucțiunea DECLARE, putem avea și control asupra fluxului și trebuie să închidem corpul funcției într-un bloc BEGIN END.

Să vedem un exemplu de funcție scrisă în acest limbaj:

 CREAȚI FUNCȚIA sel_logs_rt (param_user_name varchar) TABEL RETURNĂ (log_id int, user_name varchar (50), text descriere, log_ts timestamptz) AS $$ BEGIN RETURN QUERY SELECT log_id, user_name, description, log_ts FROM logs WHERE user_name = param_user_name; SFÂRȘIT; $$ LIMBA 'plpgsql' STABIL; 

Acum să vedem cum să scriem funcții cu Python.

Scrierea funcțiilor cu Python
Python este un limbaj de programare destul de curat, care are un număr mare de biblioteci disponibile.
PostgreSQL este singurul motor de baze de date care vă permite să utilizați Python pentru a construi funcții.

Pentru a obține posibilitatea de a crea funcții cu Python, trebuie mai întâi să ne asigurăm că avem limba instalată pe serverul nostru. Odată ce știm că îl avem instalat, trebuie să activăm extensiile din PostgreSQL folosind următoarele comenzi:

 CREAȚI EXTENSIE plpython2u; CREAȚI EXTENSIUNE plpython3u; 

Trebuie să ne asigurăm că Python funcționează înainte de a activa extensiile pentru a evita erorile.

Funcții de bază cu Python
Odată ce avem totul activat pentru a putea folosi Python, vom începe să ne construim funcția, este important să știm că PostgreSQL își poate converti tipurile de date în tipuri de date Python și invers. PL / Python este chiar capabil să returneze tablouri și tipuri de compuși.

Să vedem mai jos o funcție care efectuează o căutare text într-o resursă online, ceva ce nu s-a putut face cu PL / pgSQL, în imaginea următoare vom vedea codul și apoi vom face explicația corespunzătoare.

• Importăm bibliotecile pe care le vom folosi.
• Căutăm pe web concatenând parametrii de intrare ai utilizatorului.
• Citim răspunsul și îl salvăm într-un fișier HTML numit raw_html.
• Salvăm partea din HTML care începe și se termină înainte de începerea.
• Eliminăm etichetele HTML și spațiul alb și salvăm din nou variabila numită rezultat.
• Întoarcem rezultatul final.
• O altă caracteristică interesantă a utilizării Python este că putem interacționa direct cu sistemul de operare, să vedem o funcție care face o listă de directoare, trebuie remarcat faptul că aceasta trebuie creată de un superutilizator:

 CREAȚI SAU ÎNLOCUIȚI FUNCȚIA list_incoming_files () RETURSE SETOF text AS $$ import os return os.listdir ('/ incoming') $$ LANGUAGE 'plpython2u' VOLATILE SECURITY DEFINER;

La ce folosește acest lucru? Ne putem întreba, pentru că imaginați-vă că dorim să consultăm fișierele pe care le avem disponibile într-un sistem, apelul la funcție ar fi cam așa:

 SELEGEȚI numele fișierului din list_incoming_files () Ca nume de fișier UNDE numele de fișier ILIKE '% .csv'

Cu aceasta terminăm acest tutorial, ne ocupăm deja de crearea de funcții în alte limbi în PostgreSQL, care ne oferă un câmp infinit atunci când vine vorba de îndeplinirea cerințelor noastre.