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

[postmodern-devel] How to remove trailing spaces?
by Slobodan Milnović 01 Mar '11

01 Mar '11

Hi, I have some fixed length text fields, that are for now only partially filled. For these, when I select them, I get a lot of empty space after the text, and it remains so in variables. Is there a way to "prune" the empty space, for example before the DAO is instantiated, or before the result is returned in "non-DAO" selects? BTW, my reasoning in creating fixed length text fields is that postmodern should be faster than with the fields with variable length. I can still change that.

3 5

[postmodern-devel] set
by Haris 28 Feb '11

28 Feb '11

I have a query like this: (query (sql (:update 'kupci :set 'ime (parameter "ime") 'prezime (parameter "prezime") 'adresa (parameter "adresa") and I want now for the next parameter to check if something is passed, I tried (:raw (if (/= (length (parameter "sobnost")) 0) (sql (:set 'sobnost (parameter "sobnost"))))) but it doesn't work. Do I have to use sql-compile maybe ?

1 0

[postmodern-devel] :IN/:NOT-IN on an empty :SET always results in :NULL
by J.P. Larocque 27 Feb '11

27 Feb '11

Hi, I'd like to be able to write a query similar to: (let ((set ...)) (postmodern:sql (:select ... :from ... :where (:not-in ... (:set set))))) But SET could be empty. This results in the query: (SELECT ... FROM ... WHERE (... NOT IN (NULL))) (The same happens when simply (:SET) is given.) S-SQL compiles an empty set to "(NULL)", whether the empty set is expressed statically with no arguments, or with a single argument evaluating to the empty list. The problem is that "(NULL)" does not express the empty set. The PostgreSQL IN and NOT IN operators (at least for explicit sets on the right-hand side) are defined[1] to evaluate to NULL whenever: * NULL is a member of the literal set on the right-hand side, and * The left-hand side value is not a member of the set. (Or of course the left-hand side is NULL.) [1] http://www.postgresql.org/docs/8.3/static/functions-comparisons.html#AEN154… This means that (:IN x (:SET)) and (:IN x (:SET y)), for any value of x and for y being the empty list, both always result in :NULL. The same goes for :NOT-IN. (postmodern:query (:select (:in 42 (:set))) :single) => :NULL ...where the result should be NIL (is 42 is a member of the empty set? no). (postmodern:query (:select (:not-in 42 (:set))) :single) == (postmodern:query (:select (:not (:in 42 (:set)))) :single) => :NULL ...where the result should be T (is 42 not a member of the empty set? yes). This is probably going to be tricky to solve. The above part of the PostgreSQL manual doesn't say anything about expressing an empty set on the RHS of IN/NOT IN. I tried looking through the grammar[2], but it's rather unwieldy and hard for me to follow. [2] http://git.postgresql.org/gitweb?p=postgresql.git;a=blob;f=src/backend/pars… Some people[3] suggest expressing the empty set as e.g. (SELECT 1 WHERE FALSE): > select 42 in (select 1 where false); ?column? ---------- f (1 row) [3] http://groups.google.com/group/pgsql.general/msg/736022e9c06affac?hl=en But the problem with this is that this empty set is typed: > select 'foo' in (select 1 where false); ERROR: invalid input syntax for integer: "foo" So, the tricky problem is: how does one correctly compile an empty :SET S-SQL expression to an expression of an empty set, that works in any context (that is, no matter what the data type of the LHS is)? Instead, maybe some trickery can be done in the definition for :IN. It could check to see whether the RHS is an S-SQL expression for the empty set and, if so, result in "false" (or "true" for :NOT-IN). This would be ugly, but closer to correct. (Caveat: when the LHS is null at query time, the result has to be NULL. So, when the RHS of :IN is the empty set, the expander would have to return an expression which checks to see whether the compiled LHS expression is null, and if so, result in "null" instead of "false".) Any thoughts on better ways? -- J.P. Larocque <jpl(a)thoughtcrime.us>

2 1

[postmodern-devel] sql-compile
by Haris 27 Feb '11

27 Feb '11

What am I doing wrong here: (query (sql-compile `(:update 'kupci :set ,@(list 'ime (parameter "ime")) :where (:= 'id (parameter "id"))))) I get from hunchentoot log: Database error 42883: function parameter(unknown) does not exist No function matches the given name and argument types. You might need to add explicit type casts. Query: UPDATE kupci SET ime = E'a' WHERE (id = parameter(E'id'))

2 1

[postmodern-devel] strange
by Haris 26 Feb '11

26 Feb '11

I have a query like: (query (sql-compile `(:insert-into 'kupci :set ,@(list 'vrijemeunosa (:now))))) If I try to execute it from repl it works but when called within function I get Undefined function :NOW called with arguments () ?

2 3

[postmodern-devel] :order-by
by Haris 22 Feb '11

22 Feb '11

Hi. How to use :order-by (or :desc): (:select '* :from 'table :order-by 'column) doesn't work ? Thanks

2 1

[postmodern-devel] date
by Haris 20 Feb '11

20 Feb '11

Hi. I know this may not be the right place to ask such a question, can you give me a link to some postgresql mailing list, I found them many, I don't know which one to choose ? How to enter date and time in a date field ? Is there a default value for date field like current date and time ? Thanks

3 3

[postmodern-devel] update example
by Haris 17 Feb '11

17 Feb '11

Hi. Can you just give me an update example with :where statement ? Thanks

2 1

[postmodern-devel] Patch: {} syntax for arrays should be inside ''
by Stas Boukarev 15 Feb '11

15 Feb '11

According to http://www.postgresql.org/docs/current/static/arrays.html#ARRAYS-INPUT arrays should look like '{ val1 delim val2 delim ... }', but cl-postgres sends them as {val1 ...}, the attached patch corrects this. -- With Best Regards, Stas.

2 5

[postmodern-devel] encoding problem
by Nicolas Martyanoff 15 Feb '11

15 Feb '11

I have a query which inserts text containing the '’' character (unicode U+2019). Postmodern seems to generate a correct SQL query, but I get a postgresql error: Database error 08P01: invalid message format The database was created with and UTF-8 encoding. I'm not sure where the problem is, perhaps postmodern doesn't correctly encode multibyte characters. Regards, -- Nicolas Martyanoff http://codemore.org khaelin(a)gmail.com

3 13