aggregate function as tuple argument postgres

Question

I want to pass aggregate function like min, max etc as query parameter using Tuple.

Below is my query:

"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"

But I'm getting following exception:

io.vertx.pgclient.PgException: syntax error at or near "(" at io.vertx.pgclient.impl.codec.ErrorResponse.toException(ErrorResponse.java:29) at io.vertx.pgclient.impl.codec.PrepareStatementCommandCodec.handleErrorResponse(PrepareStatementCommandCodec.java:62) at io.vertx.pgclient.impl.codec.PgDecoder.decodeError(PgDecoder.java:233) at io.vertx.pgclient.impl.codec.PgDecoder.decodeMessage(PgDecoder.java:122) at io.vertx.pgclient.impl.codec.PgDecoder.channelRead(PgDecoder.java:102) at io.netty.channel.CombinedChannelDuplexHandler.channelRead(CombinedChannelDuplexHandler.java:253) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:374) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:360) at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:352) at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1422) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:374) at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:360) at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:931) at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:163) at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:700) at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:635) at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:552) at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:514) at io.netty.util.concurrent.SingleThreadEventExecutor$6.run(SingleThreadEventExecutor.java:1044) at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74) at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) at java.lang.Thread.run(Thread.java:745)

But If I replace $5 with hardcode aggregate function it works. How can I pass aggregate function dynamically in this scenario?

RxJava code Snippet:

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);

k_rus k_rus · Accepted Answer · 2020-08-22T20:10:38

PostgreSQL allows to use parameters only as values and doesn't understand when you try to use parameters for function names, table names, etc. So you cannot pass aggregate name as a parameter.

I suggest to work around it in your application by concatenating the string value containing the aggregate function name. I guess it can be something like, but I am not sure about the exact syntax and what limitations of your environment are:

"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"

aggregate function as tuple argument postgres

1 Answers