Re: seq scan in the case of max() on the primary key column

On Jun 16, 2011, at 1:36 PM, Shaun Thomas wrote:
> /**
> * Return the Maximum INT Value for a Partitioned Table Column
> *
> * @param string  Name of Schema of the base partition table.
> * @param string  Name of the base partition table.
> * @param string  Name of column to search.
> */
> CREATE OR REPLACE FUNCTION spc_max_part_int(VARCHAR,  VARCHAR, VARCHAR)
> RETURNS INT AS
> $$
> DECLARE
> <snip>
>  SELECT INTO nParent t.oid
>    FROM pg_class t
>    JOIN pg_namespace n ON (t.relnamespace=n.oid)
>   WHERE n.nspname = sSchema
>     AND t.relname = sTable;

FWIW, instead of that, I would do this:

CREATE FUNCTION ...(
  p_parent_schema text
  , p_parent_table text
) ...
DECLARE
  c_parent_oid CONSTANT oid := (p_parent_schema || '.' || p_parent_table )::regclass;

... or ...

CREATE FUNCTION(
  p_parent text
)
DECLARE
  c_parent_oid CONSTANT oid := p_parent::regclass;


Advantages:

- ::regclass is search_path-aware, so you're not forced into providing a schema if you don't want to
- it will throw an error if it doesn't find a regclass entry
- you can cast the oid back to text: EXECUTE 'SELECT max(' ... 'FROM ' || c_parent_oid::regclass
- you can also query directly with the OID: SELECT relkind = 't' AS is_table FROM pg_class WHERE oid = c_parent_oid
--
Jim C. Nasby, Database Architect                   jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net