postgresql_pl_pgsql — συγκεντρωτικός οδηγός — DedSec Project

PostgreSQL PL/pgSQL

Συνήθης χρήση: Βάσεις και επεξεργασία δεδομένων.

Προτεινόμενη επέκταση αρχείου: .sql.

Εκτέλεση / περιβάλλον: Εκτέλεση σε PostgreSQL με άδεια δημιουργίας συναρτήσεων.

Αρχικό παράδειγμα

CREATE OR REPLACE FUNCTION greet(name text)
RETURNS text LANGUAGE plpgsql AS $
BEGIN
  RETURN 'Hello, ' || name || '!';
END; $;
SELECT greet('Ada');

Πώς διαβάζεται

Χρησιμοποίησε παραμέτρους ερωτημάτων και μεταναστεύσεις σχήματος.

Συνηθισμένο λάθος

Χρησιμοποίησε παραμέτρους ερωτημάτων και μεταναστεύσεις σχήματος. Έλεγξε την έκδοση διερμηνευτή/μεταγλωττιστή και μην εκλαμβάνεις το δείγμα ως έτοιμη εφαρμογή παραγωγής.

PostgreSQL_PL_pgSQL — Αναλυτικός οδηγός εξάσκησης

Ξεκίνησε από αληθινή σύνταξη

Το αρχικό παράδειγμα της PostgreSQL PL pgSQL είναι η μικρότερη αφετηρία. Διάβασε τη σειρά των πράξεων και τον τρόπο εμφάνισης, έπειτα επιβεβαίωσέ το στο σωστό περιβάλλον. Μην θεωρείς ότι η σύνταξη άλλης γλώσσας έχει ίδια σημασία.

CREATE OR REPLACE FUNCTION greet(name text)
RETURNS text LANGUAGE plpgsql AS $
BEGIN
  RETURN 'Hello, ' || name || '!';
END; $;
SELECT greet('Ada');

Περιβάλλον εκτέλεσης: Run in a PostgreSQL database with privileges to create functions.. Έλεγξε έκδοση compiler/interpreter πριν τροποποιήσεις τον κώδικα και κράτησε την αρχική έξοδο. Δούλεψε σε προσωρινό κατάλογο, μακριά από πραγματικά δεδομένα.

Οδηγούμενο εργαστήριο

Φτιάξε safe_double(n integer) με BEGIN/END και $ body, απόρριψε αρνητικούς με RAISE EXCEPTION και έλεγξε ξεχωριστά NULL και overflow.

Δοκιμές ορίων

Ένα μονό $ δεν οριοθετεί σωστά function body· χρησιμοποίησε dollar quoting. Έλεγξε side effects και locks.

Προχωρημένη αποστολή

Φτιάξε ξεχωριστό αρχείο για το παραπάνω εργαστήριο και πειραματίσου με μία αλλαγή σε σειρά, σχήμα, τύπο, scope ή περιβάλλον, ανάλογα με τη PostgreSQL PL pgSQL. Καθόρισε invariant και σύγκρινε αναμενόμενη με πραγματική έξοδο. Αν κάτι διαφέρει, απομόνωσε το ελάχιστο παράδειγμα πριν μεγαλώσεις το πρόγραμμα.

PostgreSQL_PL_pgSQL — Διαδρομή ουσιαστικής εξάσκησης

Έγκυρος χειρισμός μηδενικού παρονομαστή

Η PL/pgSQL επιτρέπει μεταβλητές, συνθήκες και εξαίρεσεις γύρω από SQL. Η παρακάτω function επιστρέφει numeric αποτέλεσμα αλλά απορρίπτει ρητά μη επιτρεπτή είσοδο πριν εκτελέσει division.

CREATE OR REPLACE FUNCTION safe_ratio(a numeric, b numeric)
RETURNS numeric LANGUAGE plpgsql AS $$
BEGIN
  IF b IS NULL OR b = 0 THEN
    RAISE EXCEPTION 'denominator must be nonzero'
      USING ERRCODE = '22012';
  END IF;
  RETURN a / b;
END;
$$;
SELECT safe_ratio(12, 3);

Η SELECT αναμένεται να επιστρέψει 4. Δοκίμασε safe_ratio(12,0) και κατέγραψε τον SQLSTATE, όχι μόνο το ανθρώπινο error message. Αν το a είναι NULL, η έκφραση a / b δίνει NULL, εκτός αν αποφασίσεις διαφορετική πολιτική και την υλοποιήσεις.

SQLSTATE αντί για parsing μηνύματος

Οι εφαρμογές πρέπει να βασίζονται σε κωδικούς ή δομημένα αποτελέσματα όταν χειρίζονται γνωστές αποτυχίες. Η σύγκριση με τμήμα ελεύθερου κειμένου μπορεί να σπάσει όταν αλλάξει wording ή localization. Μην καταπίνεις ανεξαιρέτως όλα τα errors μέσα σε EXCEPTION WHEN OTHERS, επειδή τότε χάνεις τη διάκριση μεταξύ λανθασμένης εισόδου και πραγματικής αποτυχίας αποθήκευσης.

Συναλλαγές και side effects

Η PL/pgSQL function τρέχει εντός της transaction context του caller και δεν μπορεί απλώς να κάνει ανεξάρτητο COMMIT σαν μια απλή απομονωμένη διαδικασία. Αν θέλεις transaction control εξετάζεις PostgreSQL procedures και τα συγκεκριμένα όριά τους. Σχεδίασε μία μικρή ενημέρωση πίνακα και επαλήθευσε rollback όταν exception τερματίζει την transaction.

Παραδοτέο

Εκτέλεσε σε προσωρινή βάση πέντε tests: έγκυρη division, b=0, b=NULL, a=NULL, και δεκαδικούς numeric operands. Πρόσθεσε client που κάνει bind parameters. Κατάγραψε PostgreSQL version και ακριβή SQLSTATE, ξεχωρίζοντας αυτό το παράδειγμα από γενική SQL engine συμπεριφορά.

PostgreSQL PL pgSQL — Τεχνικά σημεία και συχνές παγίδες

Τεχνική γνώση

1. Η PL/pgSQL προσθέτει διαδικαστικό έλεγχο στις πράξεις SQL.
2. Χρησιμοποίησε παραμέτρους στις κλήσεις και allow-lists για ονόματα σε δυναμικό SQL.
3. Έλεγξε χειρισμό εξαιρέσεων και συναλλαγές πριν αλλάξεις αποθηκευμένες συναρτήσεις.