Made this way as I've understood the linked PR:
class ColumnFunction(functions.FunctionElement):
__visit_name__ = 'function'
@property
def columns(self):
return FromClause.columns.fget(self)
def _populate_column_collection(self):
for col in self.column_defs:
self._columns[col.name] = FunctionColumn(self, col.name)
def alias(self, name=None, flat=False):
cols = ['%s %s' % (c.name, c.type.__visit_name__) for c in
self.column_defs]
return Alias(self, '%s(%s)' % (name, ', '.join(cols)))
class jsonb_to_recordset_func(ColumnFunction):
name = 'jsonb_to_recordset'
column_defs = [
Column(Integer, name='goods_id'),
Column(Integer, name='quantity')
]
then a construct as:
jr = jsonb_to_recordset_func(OrderCsExtra.supplemental_items).alias('j')
q = SQLAS.query(jr.c.goods_id).select_from(jr)
print(q.statement.compile(dialect=postgresql.dialect(),
compile_kwargs={'literal_binds': True}))
produces
SELECT "j(goods_id integer, quantity integer)".goods_id AS "j(goods_id
integer, quantity integer)_goods_id" \nFROM
jsonb_to_recordset(orders_cs_extra.supplemental_items) AS "j(goods_id
integer, quantity integer)"
So still can't figure out how to refer an alias as it's outer part of name
(that part outside the brackets) at QUERY-clause. Still it should pass the
whole construct to the FROM-clause.
понедельник, 21 марта 2016 г., 13:38:33 UTC+3 пользователь Andy S написал:
>
> I'm stuck with the need to generate a query of the form like:
>
> SELECT
> a.id,
> b.name,
> jr.*
> FROM
> a, b outer join jsonb_populate_recordset(b.jrs) as jr(bid numeric, name
> varchar) on (TRUE)
> WHERE
> a.id = b.a_id
>
> First, it's not clear to me how to construct a dynamically defined RECORD
> as 'jr(bid numeric, name varchar)' so 'jr' becomes a table name that could
> be referenced later on
>
> Then it's not clear to me how to construct a table-like object of a
> function call that one can reference in query() by it's name ('jr' in this
> case).
>
>
> Is it possible with SQLAlchemy?
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
