So everything I know about SQL says that this would be invalid SQL:
SELECT some_function(s) FROM (select <things> FROM <stuff>) AS s
because "s" is a FROM clause and "some_function()" is in the columns clause.
"s" is not a SQL column expression.
Postgresql wants it like that huh? For the moment I'd probably just hack
it, func.row_to_json(literal_column('s')).
On Oct 2, 2014, at 11:54 PM, Jon Rosebaugh <chai...@gmail.com> wrote:
> I'm having some trouble with the Postgresql function row_to_json. It's
> possible to use it on an entire table to turn each row from the table into a
> JSON object, like select row_to_json(t) as j from some_table as t. You can
> also use a subselect, and that's where the trouble lies.
>
> I want to generate this SQL, or the equivalent:
> update sample set query_args=(select row_to_json(s) from (select
> ARRAY[campaign] as campaign, ARRAY[keyword] as keyword from sample as
> inner_sample where inner_sample.id = sample.id) as s);
>
> Here's my table definition and attempt:
>
> import sqlalchemy as sa
> from sqlalchemy.dialects.postgres import array, JSON
>
> engine = sa.create_engine("postgres://foo") # let engine be a valid postgres
> engine
> meta = sa.MetaData()
> sample_table = sa.Table('sample', meta,
> sa.Column('id', sa.Integer, primary_key=True),
> sa.Column('campaign', sa.Unicode),
> sa.Column('keyword', sa.Unicode),
> sa.Column('query_args', JSON))
> sample_table.create(engine)
> # put in some sample rows
> engine.execute(sample_table.insert(), [{'campaign': 'one', 'keyword': 'foo'},
> {'campaign': 'two', 'keyword': 'bar'}])
>
> inner_sample = sample_table.alias('inner_sample')
> s = sa.select([array([inner_sample.c.campaign]).label('campaign'),
> array([inner_sample.c.keyword]).label('keyword')]).where(inner_sample.c.id ==
> sample_table.c.id).correlate(sample_table).alias('s')
> print s.compile(engine)
> # SELECT ARRAY[inner_sample.campaign] AS campaign,
> ARRAY[inner_sample.keyword] AS keyword
> # FROM sample AS inner_sample, sample
> # WHERE inner_sample.id = sample.id
>
> The FROM clause isn't quite right, but that will take care of itself later
> thanks to the correlate call. This is the subselect that I want to turn into
> JSON using row_to_json, and then store into the query_args column.
>
> j = sa.func.row_to_json(s).select().select_from(s).as_scalar()
>
> print sample_table.update().values(query_args=j).compile(db_session.bind)
>
> # UPDATE sample SET query_args=(SELECT row_to_json(SELECT
> ARRAY[inner_sample.campaign] AS campaign, ARRAY[inner_sample.keyword] AS
> keyword
> # FROM sample AS inner_sample
> # WHERE inner_sample.id = sample.id) AS row_to_json_1
> # FROM (SELECT ARRAY[inner_sample.campaign] AS campaign,
> ARRAY[inner_sample.keyword] AS keyword
> # FROM sample AS inner_sample
> # WHERE inner_sample.id = sample.id) AS s)
>
> Now the FROM clause in the subselect is right, but the subselect gets
> repeated inside the row_to_json call. The alias 's' does correctly get
> rendered; how can I tell SQLAlchemy that row_to_json is a function that can
> accept a selectable? It's basically right except the row_to_json call should
> just be row_to_json(s).
>
> --
> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
