Is it possible to pass a WITH clause alias as function argument in postgres?

Question

I would like to refactor my query to use a subquery as a function argument.

The original query looks like this and works fine:

SELECT date_trunc('hour', (SELECT timestamp FROM data ORDER BY timestamp DESC LIMIT 1));

I wonder if it's possible to refactor a query to pass an aliased value as an argument instead of the above shown SELECT statement.

I tried to come up with something like this without luck:

WITH ts_query AS (
  SELECT "timestamp" FROM "data" ORDER BY "timestamp" DESC LIMIT 1
) 
SELECT date_trunc('hour', ts_query);

As I get the following error:

ERROR: column "ts_query" does not exist

Gordon Linoff · Accepted Answer · 2020-01-17 12:40:53Z

4

Is this what you want?

WITH ts_query AS (
      SELECT "timestamp"
      FROM "data"
      ORDER BY "timestamp" DESC
      LIMIT 1
     )
SELECT date_trunc('hour', "timestamp")
FROM ts_query;

In other words ts_query replaces a table not a column in the subsequent query.

In a more complicated query, you can use:

SELECT date_trunc('hour', t1."timestamp")
FROM ts_query tq CROSS JOIN
     . . .   -- more query logic here

I do this in queries that I want to parameterize, having a CTE (called params) that contains the parameters used in the rest of the query.

If you know that the CTE has exactly one row, you can use it as a scalar subquery:

SELECT date_trunc('hour', (SELECT "timestamp" FROM ts_query) )

If the CTE has more than one row, then this will generate a run-time error.

answered Jan 17, 2020 at 12:40

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Szabolcs Over a year ago

Oh yeah, it perfectly covers me scenario, thank you!

Collectives™ on Stack Overflow

Is it possible to pass a WITH clause alias as function argument in postgres?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related