January 2011 - postmodern-devel - mailman3.common-lisp.net

[postmodern-devel] plists
by Haris 25 Jan '11

25 Jan '11

Hi. I didn't quite understand the :plists argument. I want for select to return me lists of plists. I tried this: (query "select * from table1;" :plists t) but it doesn't work ? P.S. What's with the Croatia example ? I'm from Croatia.

2 1

[postmodern-devel] Minor bug in the Simple Date reference manual
by Sebastian Tennant 25 Jan '11

25 Jan '11

Hi Marijn, There's a very minor bug in the Simple Date reference manual: http://marijnhaverbeke.nl/postmodern/simple-date.html The HTML title tag reads "Postmodern reference manual" whereas I belive it should read "Simple Date reference manual". It's noticeable if/when you create browser bookmarks for the reference manual of each component part of Postmodern. Seb -- Emacs' AlsaPlayer - Music Without Jolts Lightweight, full-featured and mindful of your idyllic happiness. http://home.gna.org/eap

2 1

[postmodern-devel] Can interval be zero?
by Slobodan Milnović 25 Jan '11

25 Jan '11

Hi, I have an situation where I have an interval of "zero" time that I would like to put with the other data in the table. When I try with the (simple-date:encode-interval :millisecond 0) I get the object > (simple-date:encode-interval :millisecond 0) > #<SIMPLE-DATE:INTERVAL P> But, when I try to insert this object into the table with the insert-dao, I get the following error (I have left out other fields, for other non-zero values of interval the insert works fine). Database error 22007: invalid input syntax for type interval: "" Query: INSERT INTO mytable (someinterval) VALUES (interval E'') RETURNING somevalue [Condition of type CL-POSTGRES-ERROR:INVALID-DATETIME-FORMAT] I'm using latest postmodern from the git repository https://github.com/marijnh/Postmodern.git For now I'll use (simple-date:encode-interval :millisecond 1), but I'm wondering if this is an issue with postmodern or postgresql?

3 5

[postmodern-devel] Bulk Copying
by Andy Chambers 25 Jan '11

25 Jan '11

Hi All, I'm trying to add support for bulk copying to postmodern. Does anyone have any ideas on how it should be done. Here's my suggestion... 1) Add message primitives (we seem to already have copy-done-message) (define-message copy-data-message #\d (data) (string data)) (define-message copy-fail-message #\f (reason) (string reason)) 2) Add a "bulk-copier" class with slots for db ; the connection we're using table ; the table we're copying from/to from/to ; whether we're copying from or to a table header ; whether to include a header row in output/input ;; plus slots for the CSV options delimiter null-str quote-str escape-str 3) A function open-copier creates an instance of the above class and sends the corresponding copy statement to Postgres before returning the object. 4) A function copy-row takes a row (i.e. a simple list of Lisp values), and writes them to the stream attached to the copier. 5) A function copy-done lets Postgres know we're finished and commits the change to the database. Anyone got any hints for debugging this low-level stuff? I seem to be able to open a copier (and send the copy statement to pg) but when I send the complete-command message in copy-done, my code is hanging. -- Andy Chambers

2 1

[postmodern-devel] id field
by Haris 24 Jan '11

24 Jan '11

Then I don't need primary key ? If I choose my primary key to be an integer then I have to keep track of numbers and automatically insert number incremented by one ? I worked long ago with Access and it automatically inserts number incremented by one as id. I guess than this is part of Access program and not the underlaying database ?

2 1

[postmodern-devel] id field
by Haris 24 Jan '11

24 Jan '11

Hi. When I try to insert some data in table with (query "insert into ...") it asks me also to enter 'id' field which is a primary key. Shouldn't primary key field be automatically filed ? I made my table with pgadmin 3. Thanks

2 1

[postmodern-devel] Syntax help requested
by Sabra Crolleton 17 Jan '11

17 Jan '11

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)))))))

2 2

[postmodern-devel] Point type
by Slobodan Milnović 02 Jan '11

02 Jan '11

Hi, What would be the best representation of the point type? So far, I have been using following for query: (:[] 'point-column 0) (:[] 'point-column 1) and this for update: 'point-column (:type (concatenate 'string (write-to-string x-coordinate) "," (write-to-string y-coordinate)) point) It works, but seems a bit of a kludge. And how about more complex data types that use points like these http://www.postgresql.org/docs/8.4/static/datatype-geometric.html ?

2 1