Database Reference
In-Depth Information
--
-- Look for the first file. Report if missing
--
SELECT (pg_stat_file(filepath)).size
INTO tablesize;
--
-- Sum the sizes of additional files, if any
--
WHILE FOUND LOOP
i := i + 1;
filename := filepath || '.' || i;
--
-- pg_stat_file returns ERROR if it cannot see file
-- so we must trap the error and exit loop
--
BEGIN
SELECT tablesize + (pg_stat_file(filename)).size
INTO tablesize;
EXCEPTION
WHEN OTHERS THEN
EXIT;
END;
END LOOP;
RETURN tablesize;
END;
$$;
which can also work on Windows with a few minor changes, left as an exercise for the reader.
Understanding object dependencies
In most databases, there will be dependencies between objects in the database. Sometimes,
we need to understand those dependencies to figure out how to perform certain actions. Let's
look at this in detail.
Getting ready
We'll use the following simple database to understand the issues and to investigate them.
There are two tables, as follows:
CREATE TABLE orders (
orderid integer PRIMARY KEY
);
