Menu

CREATE FUNCTION ו-Stored Procedures ב-PostgreSQL

CREATE FUNCTION מגדיר פונקציה שקוראים לה בתוך שאילתות, כתובה ב-SQL או ב-PL/pgSQL. ‏CREATE PROCEDURE מגדיר שגרה שמריצים עם CALL, ושיכולה לבצע commit לטרנזקציות תוך כדי ריצה.

בדף הזה יש עורכים שאפשר להריץ. לערוך, להריץ ולראות את הפלט מיד.

CREATE FUNCTION name(arg type) RETURNS type LANGUAGE sql AS $$ ... $$; מגדיר פונקציה ששאילתות יכולות לקרוא לה כמו לפונקציה מובנית. הגוף נכתב בין סימני $$, ב-SQL רגיל או ב-PL/pgSQL, השפה הפרוצדורלית של PostgreSQL. CREATE PROCEDURE ‏(PostgreSQL 11+) מגדיר במקום זה שגרה שמריצים עם CALL.

with_tax(50) משתמש בשיעור ברירת המחדל ומחזיר 60; ‏with_tax(50, 0.1) מחזיר 55. אפשר גם להעביר ארגומנטים לפי שם: with_tax(50, rate => 0.1).

לכתוב לוגיקה ב-PL/pgSQL

השתמשו ב-LANGUAGE plpgsql כשהפונקציה צריכה משתנים, IF, לולאות או טיפול בשגיאות. משתנים נכתבים בחלק DECLARE, והקוד בין BEGIN ל-END:

חבילה של 4 ק״ג עולה את הבסיס, 5; חבילת express של 20 ק״ג עולה (5 + 10 * 0.5) * 2 = 20. בתוך PL/pgSQL, ‏RAISE NOTICE 'value: %', cost; מדפיס הודעה ו-RAISE EXCEPTION עוצר עם שגיאה.

כש-SQL רגיל יכול לבטא את הלוגיקה, העדיפו LANGUAGE sql: ה-planner יכול לשלב פונקציית SQL פשוטה בתוך השאילתה הקוראת.

להחזיר קבוצת שורות

RETURNS TABLE (...) יוצר פונקציה שמחזירה שורות. קוראים לה ב-FROM כמו לטבלה:

orders_of('ana') מחזיר את הזמנות 1 ו-3. ‏RETURNS SETOF orders מחזיר שורות שלמות של הטבלה orders במקום העמודות שפורטו.

מאז PostgreSQL 14, לפונקציית SQL יכול להיות גוף SQL סטנדרטי במקום מחרוזת. PostgreSQL שומר אותו מפוענח ורושם את הטבלאות והעמודות שהוא משתמש בהן, כך שאי אפשר למחוק אותן כל עוד הפונקציה קיימת:

CREATE FUNCTION order_count(who text) RETURNS bigint
LANGUAGE sql STABLE
BEGIN ATOMIC
  SELECT count(*) FROM orders WHERE customer = who;
END;

ליצור stored procedure ולהריץ אותה עם CALL

לפרוצדורה אין טיפוס החזרה; מריצים אותה עם CALL. היא יכולה לשנות נתונים, וכשה-CALL לא נמצא בתוך בלוק טרנזקציה מפורש, היא יכולה לבצע COMMIT באמצע. כך מעבדים עבודה גדולה במנות.

חשבון 1 מסתיים עם 70 וחשבון 2 עם 80. העברה של 200 הייתה מפרה את אילוץ ה-CHECK על חשבון 1, והשגיאה מבטלת את שני העדכונים.

פונקציהפרוצדורה
מריצים עםSELECT f(...), בכל מקום בשאילתהרק CALL p(...)
מחזירהערך, שורה או קבוצת שורותכלום (או פרמטרי OUT/INOUT)
COMMIT / ROLLBACK בפניםלאכן, ב-PL/pgSQL, כשלא קוראים לה בתוך בלוק טרנזקציה
מאזתמידPostgreSQL 11

לסמן את ה-volatility: ‏IMMUTABLE, ‏STABLE, ‏VOLATILE

תווית ה-volatility אומרת ל-planner מה הוא רשאי להניח על פונקציה. ברירת המחדל היא VOLATILE.

  • IMMUTABLE: התוצאה תלויה רק בארגומנטים ואף פעם לא משתנה (with_tax). חובה לפונקציות שמשמשות בביטוי של אינדקס.
  • STABLE: התוצאה יכולה להשתנות בין פקודות אבל לא בתוך פקודה אחת, למשל פונקציה שקוראת טבלאות (orders_of).
  • VOLATILE: כל השאר, כמו פונקציות שמשתמשות ב-random() או משנות נתונים. PostgreSQL קורא לה שוב לכל שורה.

תווית שגויה נותנת תוצאות שגויות: פונקציה IMMUTABLE שקוראת טבלה יכולה להחזיר ערכים ישנים מאינדקס או מתוכנית שמורה.

להחליף, להעמיס ולמחוק פונקציה

CREATE OR REPLACE FUNCTION משנה את הגוף של פונקציה קיימת אבל לא יכול לשנות את טיפוס ההחזרה שלה; לשם כך מחקו אותה קודם. PostgreSQL מזהה פונקציה לפי השם שלה וטיפוסי הארגומנטים, כך ש-f(int) ו-f(text) הן שתי פונקציות. כדי למחוק אחת משתי פונקציות עם אותו שם, ציינו את טיפוסי הארגומנטים:

CREATE OR REPLACE FUNCTION with_tax(price numeric, rate numeric DEFAULT 0.2) ...;
DROP FUNCTION with_tax(numeric, numeric);
DROP PROCEDURE transfer;

פרק ה-PL/pgSQL מתעד לולאות, cursors, חריגות ו-SQL דינמי עם EXECUTE. כדי להריץ פונקציה אוטומטית כששורות משתנות, חברו אותה ל-טריגר.

שאלות נפוצות

מה ההבדל בין פונקציה לפרוצדורה ב-PostgreSQL?

פונקציה מחזירה ערך וקוראים לה בתוך שאילתה (SELECT f(x)). פרוצדורה, שנוספה ב-PostgreSQL 11, רצה לבדה עם CALL ויכולה להריץ COMMIT או ROLLBACK בתוך הגוף שלה; פונקציה תמיד רצה בתוך הטרנזקציה של מי שקרא לה.

מה המשמעות של $$ בפונקציה של PostgreSQL?

$$ הוא dollar quoting: הוא מסמן את ההתחלה והסוף של ליטרל מחרוזת, כך שגוף הפונקציה יכול להכיל גרשיים בודדים בלי escape. אפשר גם להוסיף תגית, כמו ב-$body$ ... $body$.

איך רואים את הקוד של פונקציה ב-PostgreSQL?

ב-psql, ‏\df מציג את רשימת הפונקציות ו-\sf name מדפיס את ההגדרה של פונקציה. ב-SQL, ‏SELECT pg_get_functiondef('name'::regproc); מחזיר את פקודת ה-CREATE OR REPLACE FUNCTION המלאה.

האם פונקציה ב-PostgreSQL יכולה להחזיר כמה שורות?

כן. הגדירו אותה עם RETURNS TABLE (col type, ...) או RETURNS SETOF type וקראו לה בפסוקית FROM כמו לטבלה: SELECT * FROM f(1);.

איור של שפות התכנות ב-Coddy

ללמוד תכנות עם Coddy

להתחיל