SQL FOUND

What is SQL FOUND?

FOUND is a built-in boolean variable in PostgreSQL PL/pgSQL that becomes TRUE when the immediately preceding SQL statement returned or affected at least one row, and FALSE otherwise.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL FOUND: PostgreSQL: Yes MySQL, SQL Server, Oracle, SQLite, Snowflake: No

SQL FOUND Full Explanation

FOUND belongs to the family of special status variables automatically maintained by the PL/pgSQL interpreter. Immediately after every SQL command that can return, move, or affect rows (SELECT INTO, INSERT, UPDATE, DELETE, FETCH, MOVE, FOR loops), PL/pgSQL sets FOUND to:• TRUE – at least one row was returned, moved, or affected.• FALSE – no rows were touched.The variable is INITIALLY NULL when a block starts and is overwritten by each qualifying command, so it must be inspected right after the statement of interest.FOUND is session-local to the executing PL/pgSQL function, anonymous DO block, or procedure. It can be reassigned manually but doing so is rare and can obscure intent. It is not visible in plain SQL, nor in other procedural languages like PL/pgSQL’s cousins (plpythonu, plv8). Outside PostgreSQL, other dialects expose similar concepts under different names (SQL%ROWCOUNT, @@ROWCOUNT, FOUND_ROWS()).

SQL FOUND Syntax

-- Used as a boolean expression
IF FOUND THEN
    -- logic when at least one row touched
END IF;

SQL FOUND Parameters

Example Queries Using SQL FOUND

-- 1. Detect missing row after DELETE
CREATE OR REPLACE FUNCTION delete_user(p_id INT)
RETURNS VOID AS $$
BEGIN
    DELETE FROM users WHERE id = p_id;
    IF NOT FOUND THEN
        RAISE NOTICE 'User % does not exist', p_id;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 2. Test result of SELECT INTO
DO $$
DECLARE
    v_user users%ROWTYPE;
BEGIN
    SELECT * INTO v_user FROM users WHERE id = 10;
    IF FOUND THEN
        RAISE NOTICE 'User 10 exists';
    END IF;
END$$;

-- 3. Loop until cursor exhausted
DO $$
DECLARE
    v_row RECORD;
    cur CURSOR FOR SELECT * FROM users;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO v_row;
        EXIT WHEN NOT FOUND;
        -- process row here
    END LOOP;
    CLOSE cur;
END$$;

Expected Output Using SQL FOUND

FOUND evaluates to TRUE or FALSE
In Example 1, if the DELETE hits no rows, the function prints a NOTICE
In Examples 2 and 3 the control flow branches based on FOUND without returning a result set to the client

Use Cases with SQL FOUND

Emit a warning when UPDATE/DELETE touches zero rows.
Decide whether to insert or update after attempting an UPDATE.
Stop looping over a cursor when no more rows are fetched.
Quickly test existence of a row fetched with SELECT INTO.

Common Mistakes with SQL FOUND

Checking FOUND long after multiple SQL commands – it only reflects the most recent qualifying command.
Expecting FOUND to change after statements that do not move or affect rows (e.g., CREATE TABLE).
Using FOUND in plain SQL (it is available only inside PL/pgSQL code blocks).

First Introduced In

PostgreSQL 7.0

Frequently Asked Questions

Is FOUND a reserved keyword?

No. FOUND is just a predefined PL/pgSQL variable and can still be used as an identifier in plain SQL objects.

Can I manually set FOUND?

Yes, you can assign TRUE or FALSE to FOUND, but doing so is unusual and may confuse readers because the interpreter will overwrite it after the next SQL statement.

Does FOUND work after COPY or CREATE TABLE AS?

No. Only commands that inherently deal with row counts (SELECT INTO, INSERT, UPDATE, DELETE, FETCH, MOVE, FOR) update FOUND.

What is the difference between FOUND and GET DIAGNOSTICS ROW_COUNT?

FOUND is a simple boolean flag. GET DIAGNOSTICS ROW_COUNT returns the exact number of rows affected, giving you more granular information when you need the count rather than a boolean.