[postmodern-devel] Syntax help requested

[Sabra Crolleton]

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)))))))
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mailman.common-lisp.net/pipermail/postmodern-devel/attachments/20110115/4c660749/attachment.html>