CODEWITHSUNDEEP
postgresqlaggregate-functionsrx-java2vert.xtimescaledb
oOXAam
oOXAam

Reputation: 305

aggregate function as tuple argument postgres

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

Upvotes: 0

Views: 656

Answers (1)

k_rus
k_rus

Reputation: 3219

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"

Upvotes: 2

Related Questions