Stored Procedures, Functions and Triggers in PL/pgSQL

Previously, in Part 4, we built the phrasebook for everyday queries. Now for the question every SQL Server developer asks about five minutes into a migration: what happens to my stored procedures?

The honest answer: they survive, but they emigrate as two different species. SQL Server has one big tent — a stored procedure can return result sets, set output parameters, manage transactions, and do all three in the same batch. PostgreSQL splits that tent firmly in two:

• Functions return values, can be used inside a SELECT, and cannot commit or roll back transactions.
• Procedures (created with CREATE PROCEDURE, invoked with CALL) don't return a result the same way, but can COMMIT and ROLLBACK mid-flight — handy for batch jobs that checkpoint as they go.

Anatomy of a PL/pgSQL function

Here's the smallest useful specimen, annotated where it differs from T-SQL:

CREATE FUNCTION days_until(due date)   -- parameters are just names: no @ prefix
RETURNS int                            -- the return type is declared up front
LANGUAGE plpgsql                       -- the body's language, stated explicitly
AS $$                                  -- $$ starts a "dollar-quoted" string
DECLARE
    remaining int;                     -- all variables live in one DECLARE block
BEGIN
    remaining := due - current_date;   -- assignment is := , not SET
    RETURN remaining;
END;
$$;                                    -- and $$ ends it

The $$ … $$ pair is the bit that looks strangest at first. The entire function body is passed to PostgreSQL as one string literal, and dollar-quoting is just a way of writing a string without having to escape every single quote inside it. Once you've seen it, you can't unsee it — it's quotation marks wearing a trench coat. LANGUAGE plpgsql matters because Postgres supports several body languages; for a simple single-query function you can use LANGUAGE sql instead, which the planner can often inline.

The centerpiece: one procedure, translated line by line

Here's a realistic T-SQL procedure of the kind you've written a hundred times — validate, insert, hand back the new id:

-- SQL Server
CREATE PROCEDURE dbo.usp_AddAppointment
    @PatientId INT,
    @StartsAt  DATETIME2,
    @NewId     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF NOT EXISTS (SELECT 1 FROM dbo.Patients WHERE PatientId = @PatientId)
    BEGIN
        RAISERROR('Unknown patient.', 16, 1);
        RETURN;
    END

    BEGIN TRY
        INSERT INTO dbo.Appointments (PatientId, StartsAt)
        VALUES (@PatientId, @StartsAt);

        SET @NewId = SCOPE_IDENTITY();
        PRINT 'Appointment created.';
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
        THROW;
    END CATCH
END

And the same logic as a PL/pgSQL function:

-- PostgreSQL
CREATE FUNCTION add_appointment(p_patient_id int, p_starts_at timestamptz)
RETURNS int
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_id int;
BEGIN
    IF NOT EXISTS (SELECT 1 FROM patients WHERE patient_id = p_patient_id) THEN
        RAISE EXCEPTION 'Unknown patient: %', p_patient_id;
    END IF;

    INSERT INTO appointments (patient_id, starts_at)
    VALUES (p_patient_id, p_starts_at)
    RETURNING appointment_id INTO v_new_id;

    RAISE NOTICE 'Appointment % created.', v_new_id;
    RETURN v_new_id;
EXCEPTION
    WHEN unique_violation THEN
        RAISE NOTICE 'That slot is already booked.';
        RETURN NULL;
END;
$$;

-- and you call it like this:
SELECT add_appointment(42, '2026-09-01 10:00');

Walking the differences:

• Variables lose the @. Parameters and locals are plain identifiers, so conventions like p_ for parameters and v_ for variables keep them from colliding with column names.
• SET becomes :=. Assignment gets its own operator, leaving = to mean comparison.
• SELECT-into-a-variable becomes SELECT … INTO v_x — or, as here, RETURNING … INTO, which grabs the new id in the same statement as the insert. No SCOPE_IDENTITY() dance.
• PRINT becomes RAISE NOTICE, with % placeholders for values. Same family, different severities: RAISE EXCEPTION is your RAISERROR/THROW.
• BEGIN TRY / BEGIN CATCH becomes an EXCEPTION WHEN section at the end of the block, matching on error conditions like unique_violation by name instead of by error number.
• The OUTPUT parameter simply disappears — the function returns the id, which is what you wanted all along.

Table-valued functions

Your TVF instinct maps to RETURNS TABLE plus RETURN QUERY:

CREATE FUNCTION get_patient_appointments(p_patient_id int)
RETURNS TABLE (appointment_id int, starts_at timestamptz)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT a.appointment_id, a.starts_at
    FROM appointments a
    WHERE a.patient_id = p_patient_id;
END;
$$;

SELECT * FROM get_patient_appointments(42);

Just like an inline TVF, it sits in the FROM clause and joins like a table.

Triggers: the two-piece shape

In SQL Server a trigger is one object. In PostgreSQL it's two: a function that returns the special type trigger, and a CREATE TRIGGER statement that binds it to a table and an event. The upside is reuse — one trigger function can serve many tables. Here's the classic updated_at stamp:

CREATE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();   -- NEW and OLD play the roles of inserted/deleted
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_touch_updated_at
BEFORE UPDATE ON appointments
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Note FOR EACH ROW: PostgreSQL triggers default to row-level thinking, where T-SQL triggers fire once per statement with the inserted and deleted pseudo-tables. Statement-level triggers exist too (FOR EACH STATEMENT), but the row-level shape with NEW and OLD is the one you'll reach for most.

You now speak the language and can rebuild the logic. Next comes the part that changes how you think rather than how you type: PostgreSQL has no clustered indexes, and its concurrency model makes NOLOCK a word you'll never write again. That's Part 6: indexes, MVCC and performance.