我有一个纯SQL函数,如下所示:
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;
它的行为和预期的一样,一切正常。但是,我想让它与
building_types
,而不是单个值。当我尝试此选项时:
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;
我得到一个语法错误:
ERROR: syntax error at or near "$1"
LINE 15: bldg."LandUse" IN $1
有什么想法吗?
如果相关的话,版本是9.6。