Re: Patch to add support for partial indices

On Mon, Jul 16, 2001 at 09:50:30PM +1000, Martijn van Oosterhout wrote:
> Well, I thought it'd be easy but a whole bunch of commands use the
> listTables function which is great if they all output the same. However, to
> add one column is not so easy. I mean, while I'm at it I could show the
> query defining a view.

Still not sure about that.

> As for showing column names, I think you'd need a function to take a list of
> names returned by a query and turn it into an array. Maybe there is such a
> beast, but I havn't seen it yet.

Well, what about this:

CREATE FUNCTION textjoin(text,text)
  RETURNS text
  AS 'SELECT CASE WHEN $1 IS NULL THEN $2 ELSE $1 || \', \' || $2 END;'
  LANGUAGE 'sql';
CREATE AGGREGATE joinlist ( basetype = text, sfunc = textjoin, stype = text );

SELECT c.relname as "Name",
       CASE c.relkind WHEN 'r' THEN 'table'
                      WHEN 'v' THEN 'view'
                      WHEN 'i' THEN 'index'
                      WHEN 'S' THEN 'sequence'
                      WHEN 's' THEN 'special'
       END as "Type",
       u.usename as "Owner",
       joinlist(attname::text) as "Attributes",
       pg_get_expr(indpred,indrelid) as "Predicate"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid,
       pg_index i, pg_attribute a
WHERE c.oid = i.indexrelid
AND i.indexrelid = a.attrelid
GROUP BY "Name", "Type", "Owner", "Predicate";

Produces an output like:

              Name               | Type  |  Owner   |             Attributes              |      Predicate
---------------------------------+-------+----------+-------------------------------------+----------------------
 pg_aggregate_name_type_index    | index | postgres | aggname, aggbasetype                |
 pg_am_name_index                | index | postgres | amname                              |
 pg_operator_oprname_l_r_k_index | index | postgres | oprname, oprleft, oprright, oprkind |
 test2                           | index | kleptog  | clid                                | (billid < '3'::text)

Only much longer ofcourse. And with a bit more work you can also show if
it's a unique index or not. Where do you draw the line?

--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> It would be nice if someone came up with a certification system that
> actually separated those who can barely regurgitate what they crammed over
> the last few weeks from those who command secret ninja networking powers.