PL/pgSQL চিটশিট

DO $$
BEGIN
    -- কোড এখানে
    RAISE NOTICE 'হ্যালো, PL/pgSQL!';
END;
$$;

DO $$
DECLARE
    my_var INTEGER := 10; -- ডিফল্ট মান সহ
    name TEXT; -- শুধু ডিক্লেয়ার
BEGIN
    name := 'রহিম';
    RAISE NOTICE 'মান: %, নাম: %', my_var, name;
END;
$$;

DO $$
DECLARE
    PI CONSTANT NUMERIC := 3.14159;
BEGIN
    RAISE NOTICE 'PI: %', PI;
END;
$$;

DO $$
DECLARE
    age INTEGER := 20;
BEGIN
    IF age >= 18 THEN
        RAISE NOTICE 'প্রাপ্তবয়স্ক';
    ELSIF age > 0 THEN
        RAISE NOTICE 'শিশু';
    ELSE
        RAISE NOTICE 'অবৈধ বয়স';
    END IF;
END;
$$;

DO $$
DECLARE
    grade CHAR := 'A';
BEGIN
    CASE grade
        WHEN 'A' THEN RAISE NOTICE 'উৎকৃষ্ট';
        WHEN 'B' THEN RAISE NOTICE 'ভালো';
        ELSE RAISE NOTICE 'অজানা';
    END CASE;
END;
$$;

DO $$
DECLARE
    i INTEGER := 0;
BEGIN
    FOR i IN 1..3 LOOP
        RAISE NOTICE 'গণনা: %', i;
    END LOOP;
END;
$$;

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
    RETURN a + b;
END;
$$ LANGUAGE plpgsql;
 
-- কল করা
SELECT add_numbers(5, 3); -- ফলাফল: 8

CREATE FUNCTION get_info(IN id INTEGER, OUT name TEXT, OUT age INTEGER) AS $$
BEGIN
    SELECT s.name, s.age INTO name, age
    FROM students s WHERE s.id = get_info.id;
END;
$$ LANGUAGE plpgsql;
 
SELECT * FROM get_info(1);

DROP FUNCTION add_numbers;

CREATE FUNCTION log_update()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'আপডেট হয়েছে: %', NEW.name;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT,
    age INTEGER
);
 
CREATE TRIGGER update_log
BEFORE UPDATE ON students
FOR EACH ROW
EXECUTE FUNCTION log_update();

DO $$
DECLARE
    student_cursor CURSOR FOR SELECT name, age FROM students;
    student_record RECORD;
BEGIN
    OPEN student_cursor;
    LOOP
        FETCH student_cursor INTO student_record;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'নাম: %, বয়স: %', student_record.name, student_record.age;
    END LOOP;
    CLOSE student_cursor;
END;
$$;

DO $$
BEGIN
    RAISE NOTICE 'শুরু';
    PERFORM 1 / 0; -- এরর ঘটবে
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'শূন্য দিয়ে ভাগ করা যায় না!';
    WHEN OTHERS THEN
        RAISE NOTICE 'অজানা এরর: %', SQLERRM;
END;
$$;

DO $$
DECLARE
    table_name TEXT := 'students';
BEGIN
    EXECUTE 'SELECT * FROM ' || table_name || ' WHERE age > 20';
END;
$$;

DO $$
DECLARE
    result_name TEXT;
BEGIN
    EXECUTE 'SELECT name FROM students WHERE id = $1' INTO result_name USING 1;
    RAISE NOTICE 'নাম: %', result_name;
END;
$$;

DO $$
DECLARE
    student_row students%ROWTYPE;
BEGIN
    SELECT * INTO student_row FROM students WHERE id = 1;
    RAISE NOTICE 'নাম: %, বয়স: %', student_row.name, student_row.age;
END;
$$;

DO $$
BEGIN
    FOR rec IN SELECT name, age FROM students LOOP
        RAISE NOTICE 'নাম: %, বয়স: %', rec.name, rec.age;
    END LOOP;
END;
$$;

DO $$
DECLARE
    numbers INTEGER[] := ARRAY[1, 2, 3];
BEGIN
    RAISE NOTICE 'প্রথম সংখ্যা: %', numbers[1];
    numbers := numbers || 4; -- যোগ করা
    RAISE NOTICE 'নতুন অ্যারে: %', numbers;
END;
$$;

DO $$
BEGIN
    INSERT INTO students (name, age) VALUES ('আলী', 25);
    RAISE NOTICE 'ডেটা যোগ হয়েছে';
    -- COMMIT; -- স্থায়ী করতে (DO ব্লকে কাজ করে না)
    -- ROLLBACK; -- বাতিল করতে
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'এরর: %', SQLERRM;
END;
$$;

CREATE FUNCTION calculate_total_students(min_age INTEGER)
RETURNS INTEGER AS $$
DECLARE
    total INTEGER;
BEGIN
    SELECT COUNT(*) INTO total
    FROM students
    WHERE age >= min_age;
 
    IF total IS NULL THEN
        total := 0;
    END IF;
 
    RAISE NOTICE 'মোট ছাত্র (বয়স >= %): %', min_age, total;
    RETURN total;
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'এরর: %', SQLERRM;
        RETURN -1;
END;
$$ LANGUAGE plpgsql;
 
-- কল করা
SELECT calculate_total_students(20);

PERFORM insert_log('আপডেট হয়েছে');

IF FOUND THEN
    RAISE NOTICE 'ডেটা পাওয়া গেছে';
END IF;