Hello everyone,
I was doing some maintenance and decided to write some meta-functions in s-sql and ran into an issue with "any".
In this particular situation I was trying to apply it to pg_index.indkey which is an int2vector.
I wanted to get the indexed columns and their attributes from a table (Not the indexes, the indexed columns)
The following sql string seems to work, but I wanted to put it into s-sql form.
(defun list-indexed-column-and-attributes (table-name) "List the indexed columns and their attributes in a table" (when (table-exists-p table-name) (query "SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) FROM pg_index, pg_class, pg_attribute WHERE pg_class.oid = $1::regclass AND indrelid = pg_class.oid AND pg_attribute.attrelid = pg_class.oid AND pg_attribute.attnum = any(pg_index.indkey)" table-name)))
I can't seem to figure out how to generate the s-sql equivalent of "any(pg_index.indkey)".
I ended up with the following, completely avoiding "any". It seems to work, but because postgresql seems to be able to have up to 32 indexed columns per table, it looks clumsy. Any suggestions as to how I can do this better?
Sabra
(defun list-indexed-column-and- attributes-pm (table-name) "List the indexed columns and their attributes in a table" (query (:select 'pg_attribute.attname (:format_type 'pg_attribute.atttypid 'pg_attribute.atttypmod) :from 'pg_index 'pg_class 'pg_attribute :where (:and (:= 'pg_class.oid (:type table-name :regclass)) (:= 'indrelid 'pg_class.oid) (:= 'pg_attribute.attrelid 'pg_class.oid) (:or (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 0)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 1)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 2)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 3)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 4)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 5)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 6)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 7)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 8)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 9)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 10)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 11)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 12)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 13)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 14)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 15)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 16)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 17)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 18)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 19)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 20)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 21)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 23)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 24)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 25)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 26)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 27)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 28)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 29)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 30)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 31)) (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22)))))))