New DEV FAQ item - Mailing list pgsql-hackers

I have added more to the last item.

---------------------------------------------------------------------------

  9) How do I efficiently access information in tables from the backend code?

   You first need to find the tuples(rows) you are interested in. There
   are two ways. First, SearchSysCacheTuple() and related functions allow
   you to query the system catalogs. This is the preferred way to access
   system tables, because the first call to the cache loads the needed
   rows, and future requests can return the results without accessing the
   base table. Some of the caches use system table indexes to look up
   tuples. A list of available caches is located in
   src/backend/utils/cache/syscache.c.
   src/backend/utils/cache/lsyscache.c contains many column-specific
   cache lookup functions.

   The rows returned are cached-owned versions of the heap rows. They are
   invalidated when the base table changes. Because the cache is local to
   each backend, you may use the pointer returned from the cache for
   short periods without making a copy of the tuple. If you send the
   pointer into a large function that will be doing its own cache
   lookups, it is possible the cache entry may be flushed, so you should
   use SearchSysCacheTupleCopy() in these cases, and pfree() the tuple
   when you are done.

   If you can't use the system cache, you will need to retrieve the data
   directly from the heap table, using the buffer cache that is shared by
   all backends. The backend automatically takes care of loading the rows
   into the buffer cache.

   Open the table with heap_open(). You can then start a table scan with
   heap_beginscan(), then use heap_getnext() and continue as long as
   HeapTupleIsValid() returns true. Then do a heap_endscan(). Keys can be
   assigned to the scan. No indexes are used, so all rows are going to be
   compared to the keys, and only the valid rows returned.

   You can also use heap_fetch() to fetch rows by block number/offset.
   While scans automatically lock/unlock rows from the buffer cache, with
   heap_fetch(), you must pass a Buffer pointer, and ReleaseBuffer() it
   when completed. Once you have the row, you can get data that is common
   to all tuples, like t_ctid and t_oid, by mererly accessing the
   HeapTuple structure entries. If you need a table-specific column, you
   should take the HeapTuple pointer, and use the GETSTRUCT() macro to
   access the table-specific start of the tuple. You then cast the
   pointer as a Form_pg_proc pointer if you are accessing the pg_proc
   table, or TypeTupleForm if you are accessing pg_type. You can then
   access the columns by using a structure pointer:

        ((Form_pg_class) GETSTRUCT(tuple))->relnatts

   You should not directly change live tuples in this way. The best way
   is to use heap_tuplemodify() and pass it your palloc'ed tuple, and the
   values you want changed. It returns another palloc'ed tuple, which you
   pass to heap_replace(). You can delete tuples by passing the tuple's
   t_ctid to heap_destroy(). Remember, tuples can be either system cache
   versions, which may go away soon after you get them, buffer cache
   version, which will go away when you heap_getnext(), heap_endscan, or
   ReleaseBuffer(), in the heap_fetch() case. Or it may be a palloc'ed
   tuple, that you must pfree() when finished.

--
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)