On Tue, Jul 27, 2010 at 11:08 AM, Michael Bayer <mike...@zzzcomputing.com> wrote: > > On Jul 27, 2010, at 11:49 AM, Jon Nelson wrote: > >> I have two questions: >> >> 1. I'm using postgresql, and I sometimes need to do column type >> conversions. In postgresql, this is normally done with the ::FOO >> operator where FOO is a data *type*. Somtimes, but not usually, these >> data types are also available in function-like factories, but in this >> case that doesn't help me. SHould I use the cast(some_column, 'int') >> expression here? > > ::FOO in PG is their internal syntax for what CAST provides, yes. > >> >> 2. Frequently, I have a list of things upon which to operate. This >> list of things is not in a table, and I may want to call a function on >> this list of things. If I were writing the sql directly, I'd do >> something like this: >> >> select function_foo(BAR.x) FROM (select value1, value2, value3) BAR(x); >> >> What's the best way to emulate this with sqlalchemy? > > there's a recipe for VALUES at > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PGValues , the syntax you > have above looks unfamiliar to me but VALUES will create a lexical "table" > from a set of literals (or you can just modify that recipe to provide the > exact syntax you want).
For postgres, I'd guess that the subquery (SELECT value1, value2, ...) [AS] BAR(x) is fundamentally identical to: (VALUES (value1, value2, ...)) [AS] BAR(x) However, for some reason I'm only getting the *first* value from VALUES (only value1 is being sent to the function). The actual generated sql looks like this: SELECT some_function(subquery.column1) FROM (VALUES (value1, value2, ...)) AS subquery; Any idea what I might be doing wrong? It's not the function itself, as I tried several built-in functions. Real example: SELECT abs(numbers.column1) FROM (VALUES (-5, 0, 5, 10)) AS numbers; returns just one row. Indeed: SELECT numbers.column1 FROM (VALUES (-5, 0, 5, 10)) AS numbers; also returns just one row. Obviously, this isn't a sqlalchemy-specific thing but I'd still appreciate the help. -- Jon -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
