<div>Hello everyone,<br></div><div><br></div><div>I was doing some maintenance and decided to write some meta-functions in s-sql and ran into an issue with "any". </div><div><br></div><div>In this particular situation I was trying to apply it to pg_index.indkey which is an int2vector.</div>


<div><br></div><div>I wanted to get the indexed columns and their attributes from a table (Not the indexes, the indexed columns)</div><div><br></div><div>The following sql string seems to work, but I wanted to put it into s-sql form.</div>


<div><br>(defun list-indexed-column-and-attributes (table-name)<br>   "List the indexed columns and their attributes in a table"<br>    (when (table-exists-p table-name)<br>          (query "SELECT pg_attribute.attname, format_type(pg_attribute.atttypid, pg_attribute.atttypmod) </div>


<div>                      FROM pg_index, pg_class, pg_attribute </div><div>                      WHERE pg_class.oid = $1::regclass </div><div>                      AND indrelid = pg_class.oid </div><div>                      AND pg_attribute.attrelid = pg_class.oid </div>


<div>                      AND pg_attribute.attnum = any(pg_index.indkey)" </div><div>           table-name)))<br></div><div><br></div><div>I can't seem to figure out how to generate the s-sql equivalent of "any(pg_index.indkey)".</div>


<div><br></div><div>I ended up with the following, completely avoiding "any". It seems to work, but because postgresql seems to be able to have</div><div>up to 32 indexed columns per table, it looks clumsy. Any suggestions as to how I can do this better?<br>

<br>Sabra<br></div>
<div><br></div>(defun list-indexed-column-and-<div id=":2">attributes-pm (table-name) <br>    "List the indexed columns and their attributes in a table"<br>  (query <br>     (:select 'pg_attribute.attname <br>

                        (:format_type 'pg_attribute.atttypid 'pg_attribute.atttypmod) <br>                        :from 'pg_index 'pg_class 'pg_attribute <br>                        :where (:and (:= 'pg_class.oid (:type table-name :regclass)) <br>

                                                 (:= 'indrelid 'pg_class.oid)<br>                                                 (:= 'pg_attribute.attrelid 'pg_class.oid)<br>                                                 (:or (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 0))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 1))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 2))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 3))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 4))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 5))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 6))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 7))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 8))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 9))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 10))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 11))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 12))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 13))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 14))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 15))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 16))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 17))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 18))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 19))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 20))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 21))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 23))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 24))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 25))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 26))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 27))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 28))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 29))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 30))<br>

                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 31))<br>                                                            (:= 'pg_attribute.attnum (:[] 'pg_index.indkey 22)))))))</div>