Use different subquery depending on variable

Question

I am trying to change the subquery used in the expression, depending on the value of a variable I am passing to the sql.

I have tried a few different ways with no success. The following sql throws the error: subquery must return only one column

WITH
sel_cells As (
    SELECT
        CASE WHEN cast (RIGHT( variable, 1 ) As int)>1 THEN (
            SELECT part_2.geom, part_2.gridcode
            FROM adm2 AS part_1, grid_1km_europe AS part_2
            WHERE part_1.gid = 7224
            AND ST_Intersects(part_1.geom, part_2.geom)
        ) ELSE (
            SELECT part_2.geom, part_2.gridcode
            FROM grid_1km_europe As part_2
            INNER JOIN grid_1km_europe_adm2 As part_1
            ON part_1.gridcode = part_2.gridcode
            WHERE part_1.adm_gid = 7224
        )
        END
),
emissions_part As (
    SELECT grid_id_1km, emissions_kg
    FROM emissions
    WHERE year_ = 2015 AND sector = 'Energy' AND pollutant = 'PM10'
)
SELECT
    a.emissions_kg,
    a.grid_id_1km,
    b.geom
FROM emissions_part As a
INNER JOIN sel_cells As b 
ON a.grid_id_1km = b.gridcode

I am using Postgres.

What is the correct way to do this?

Thank you for your help!

This query is quite big..can you specify with which portion of query you are having issue? — codeLover
– codeLover, Commented Aug 27, 2018 at 8:37
The problem is at the sel_cells part. If I didn't use CASE and instead had one of the two subqueries, the query would work fine. — gijo
– gijo, Commented Aug 27, 2018 at 8:52

Gordon Linoff · Accepted Answer · 2018-08-27 10:48:41Z

2

You can use union all:

with sel_cells As (
      select part_2.geom, part_2.gridcode
      from adm2 AS part_1 join
           grid_1km_europe as part_2
           on ST_Intersects(part_1.geom, part_2.geom)
      where part_1.gid = 7224 and
            right(variable, 1)::int > 1
      union all
      select part_2.geom, part_2.gridcode
      from grid_1km_europe As part_2 inner join
           grid_1km_europe_adm2 As part_1
           on part_1.gridcode = part_2.gridcode
      where part_1.adm_gid = 7224 and
            right(variable, 1)::int <= 1
     )

answered Aug 27, 2018 at 10:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

gijo Over a year ago

Perfect!! Thank you

404 · Accepted Answer · 2018-08-27 09:20:31Z

0

Easiest way to do this would be with plpgsql. Create a function that builds the query dynamically based on the variable, and returns you the records:

CREATE OR REPLACE FUNCTION f1(some_var BOOLEAN)
        RETURNS TABLE (g INTEGER, h INTEGER) AS
$BODY$
BEGIN
        RETURN QUERY EXECUTE
                'WITH x AS ('
                || CASE WHEN some_var THEN
                        'SELECT g AS g, g AS h FROM generate_series(1, 5) g'
                   ELSE
                        'SELECT g AS g, g AS h FROM generate_series(6, 10) g'
                   END
                || ')
                SELECT g.*, x.h
                FROM generate_series(1, 10) g
                INNER JOIN x ON g.g = x.g';
END
$BODY$
        LANGUAGE plpgsql STABLE;

Then SELECT * FROM f1(TRUE) returns:

And SELECT * FROM f1(FALSE) returns:

answered Aug 27, 2018 at 9:20

404

8,7622 gold badges33 silver badges53 bronze badges

6 Comments

gijo Over a year ago

I am not sure if I can do that, since the sql is stored in Geoserver as sql view

404 Over a year ago

I have no idea what Geoserver is. What does it mean in as far as what you can and can't do?

gijo Over a year ago

It means that I cannot call a function (probably, not 100% sure). Like I said in another comment, the query will work without the CASE. I need a correct syntax that would allow me change the subquery depending on the variable. Is this something not possible??

404 Over a year ago

If this is a view, how are you using a variable in it? Where's that variable coming from?

gijo Over a year ago

Without going into details, I can pass variables from my code to Geoserver, before the query is executed.

|

Collectives™ on Stack Overflow

Use different subquery depending on variable

2 Answers 2

1 Comment

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related