聚合函数作为元组参数postgres

Question

我想传递聚合函数，如min，max等作为查询参数使用元组。

"select  $5(CAST (vol AS FLOAT)) AS agg_v, " 
                    + "time_bucket_gapfill" + "(($1::text || ' minutes')::interval, t) AS time_function_minute, " 
                    + "tag_id from rtdata "
                    + "where tag_id = any($2) and t > $3 and t < $4 "
                    + "GROUP BY (tag_id, time_function_minute) ORDER BY time_function_minute"

pgException：在io.vertx.pgclient.impl.codec.errorResponse.toException（errorResponse.java:29)在io.vertx.pgclient.impl.codec.prepareStatementCommandCodec.handleerrorResponse（prepareStatementCommandCodec.java:62)在io.vertx.pgclient.impl.codec.pgDecoder.decodeError(PgDecoder.java:233)在.pgdecoder.channelRead(Pgdecoder.java:102)在io.netty.channel.combinedChannelDuplexHandler.java:253)在io.netty.channel.abstractChannelContext.invokeChannelRead(abstractChannelHandlerContext.java:374)在io.netty.channel.abstractChannelContext.java:360)在io.netty.channel.abstractChannelContext.java:352)在管道$HeadContext.ChannelRead（DefaultChannelPipeline.java：1422）在io.netty.channel.abstractChannelHandlerContext.invokeChannelRead（AbstractChannelHandlerContext.java:374)，在io.netty.channel.defaultChannelContext.invokeChannelRead（AbstractChannelHandlerContext.java:360)，在io.netty.channel.defaultChannelpipeline.fireChannelRead（AbstractChannelHandlerContext.java:931)，在Eventloop.java:700)在io.netty.channel.nio.nio.nioEventloop.processSelectedKeysOptimed(NioEventloop.java:635)在io.netty.channel.nio.nio.nioEventloop.processSelectedKeys(NioEventloop.java:552)在io.netty.channel.nio.nioEventloop.run(NioEventloop.java:514)在threadLocalRunnable.run(FastThreadLocalRunnable.java:30)位于java.lang.thread.run(thread.java:745)

但是如果我用hardcode聚合函数替换$5，它就可以工作了。在此场景中如何动态传递聚合函数？

RxJava代码片段：

return txBegin()
                .flatMapObservable(tx -> 
                    tx.rxPrepare(abovesql)
                    .flatMapObservable(pq -> {
                        return pq.createStream(50, 
                                                Tuple.of(
                                                    evalBucketInterval(req),
                                                    req.getTags().toArray(new Integer[0]),
                                                    parse(req.getStartDate()), 
                                                    parse(req.getEndDate()),
                                                    parse(req.getAggFunc())))
                                    .toObservable();
                    })
                .doAfterTerminate(tx::commit))
            .map(this::toFuncJson);

Answer 1

PostgreSQL只允许将参数用作值，并且不理解何时将参数用作函数名、表名等，因此不能将聚合名作为参数传递。

我建议在应用程序中通过连接包含聚合函数名的字符串值来解决这个问题。我想它可能是类似的，但我不确定确切的语法和您的环境的限制是什么：

"select  "+ my_agg_func_name +"(CAST (vol AS FLOAT)) AS agg_v, " 
                    + "time_bucket_gapfill" + "(($1::text || ' minutes')::interval, t) AS time_function_minute, " 
                    + "tag_id from rtdata "
                    + "where tag_id = any($2) and t > $3 and t < $4 "
                    + "GROUP BY (tag_id, time_function_minute) ORDER BY time_function_minute"