CODEWITHSUNDEEP
sqlpostgresqlsubquery
gijo
gijo

Reputation: 45

Use different subquery depending on variable

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!

Upvotes: 1

Views: 546

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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
     )

Upvotes: 2

404
404

Reputation: 8562

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:

enter image description here

And SELECT * FROM f1(FALSE) returns:

enter image description here

Upvotes: 0

Related Questions