Also, I still can pass a string 'j.goods_id' to query() call this fixes the
first error but then PG query compiler says it can't find a RECORD
definition for the jsonb_ function call that is required according to it's
API. I think that's because of double-quoting of the alias'ed expression in
the FROM-clause generated (it should pass w/o error if not quoted). Is
there any way to suppress quoting for alias() constructs?
вторник, 22 марта 2016 г., 12:37:23 UTC+3 пользователь Andy S написал:
>
> 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.
