How to check if value in input array in PostgreSQL function

Question

I have a pure SQL function like this:

CREATE OR REPLACE FUNCTION get_buildings_by_type(
    building_types TEXT
)

RETURNS TABLE (bldg_id TEXT, "SurfArea" FLOAT, geom GEOMETRY) AS

$$
    SELECT
        bldg."OBJECTID"::TEXT AS bldg_id,
        bldg."SurfArea"::FLOAT,
        bldg.geom
    FROM
        static.buildings AS bldg
    WHERE
        bldg."LandUse" = $1
$$

LANGUAGE SQL;

And it behaves as expected, everything is working. However, I would like to have it work with an input array of building_types, rather than a single values. When I try this instead:

CREATE OR REPLACE FUNCTION get_buildings_by_type(
    building_types TEXT[]
)

RETURNS TABLE (bldg_id TEXT, "SurfArea" FLOAT, geom GEOMETRY) AS

$$
    SELECT
        bldg."OBJECTID"::TEXT AS bldg_id,
        bldg."SurfArea"::FLOAT,
        bldg.geom
    FROM
        static.buildings AS bldg
    WHERE
        bldg."LandUse" IN $1
$$

LANGUAGE SQL;

I get a syntax error:

ERROR:  syntax error at or near "$1"
LINE 15:   bldg."LandUse" IN $1

Any ideas?

The version is 9.6 if that is relevant.

@a_horse_with_no_name: Still the same syntax error. I put the version in too since it might matter. — wfgeo
– wfgeo, Commented Nov 19, 2018 at 13:43
in operator is not for arrays, @a_horse_with_no_name posted the correct answer — JustMe
– JustMe, Commented Nov 19, 2018 at 13:45
@a_horse_with_no_name answer is indeed correct, I think I was executing in the wrong window before. — wfgeo
– wfgeo, Commented Nov 19, 2018 at 13:48

user330315 · Accepted Answer · 2018-11-19 15:59:35Z

1

The equivalent of an IN operator for arrays is the any operator:

You need to use:

WHERE
    bldg."LandUse" = any($1);

answered Nov 19, 2018 at 15:59

user330315

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

Collectives™ on Stack Overflow

How to check if value in input array in PostgreSQL function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related