I was looking into making queries for a particular
file in a Bacula Postgres database, and came up with the attached
functions. If someone really wanted to do searches by other attributes, it
would be simple to follow the same pattern. With the functions and
functional indexes, a query like
SELECT
bacula_lstat_mtime(lstat),path.path,filename.name
FROM file
INNER JOIN path ON file.pathid=path.pathid
\
INNER JOIN filename ON
file.filenameid=filename.filenameid
WHERE bacula_lstat_mtime(lstat)<'2009-12-05
13:55'
ORDER BY bacula_lstat_mtime(lstat) DESC LIMIT
15
is really fast.
--
DLL
|