plpgsql动态添加select查询到变量
plpgsql动态添加select查询到变量
提问于 2021-01-08 01:33:09
我试图循环遍历具有许多模式的varchar数组，并动态构造大量的select语句。前两个raise通知运行良好，但我不知道如何将动态select语句附加到一个大型select语句中。
理想情况下，我想要这样的东西。
SELECT
st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom
,parcel_id
FROM schema1.parcel t
JOIN bounds b
ON st_intersects(t.geom,st_transform(b.geom,4269))
UNION
SELECT
st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom
,parcel_id
FROM schema2.parcel t
JOIN bounds b
ON st_intersects(t.geom,st_transform(b.geom,4269))代码
DO
$do$
DECLARE
a varchar[] := array['schema1','schema2'];
i integer;
slct varchar;
BEGIN
FOR i IN 1 .. array_upper(a, 1)
LOOP
RAISE NOTICE '%', a[i];
RAISE NOTICE 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
FROM %.parcel t join bounds b
on st_intersects(t.geom,st_transform(b.geom,4269))',a[i];
EXECUTE 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
FROM %.parcel t join bounds b
on st_intersects(t.geom,st_transform(b.geom,4269))',a[i] into slct;
END LOOP;
END
$do$;错误
ERROR: query "SELECT 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
FROM %.parcel t join bounds b
on st_intersects(t.geom,st_transform(b.geom,4269))',a[i]" returned 2 columns
CONTEXT: PL/pgSQL function inline_code_block line 17 at EXECUTE
SQL state: 42601已更新qry和错误
EXECUTE 'SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
FROM $1.parcel t join bounds b
on st_intersects(t.geom,st_transform(b.geom,4269))' into slct
using a[i];错误
ERROR: syntax error at or near "$1"
LINE 2: FROM $1.parcel t join bounds b
^
QUERY: SELECT st_asmvtgeom(st_transform(t.geom,3857),b.geom) AS geom,parcel_id
FROM $1.parcel t join bounds b
on st_intersects(t.geom,st_transform(b.geom,4269))
CONTEXT: PL/pgSQL function inline_code_block line 17 at EXECUTE
SQL state: 42601Stack Overflow用户
回答已采纳
发布于 2021-01-08 06:13:18
如果您的模式数量有限，则可以使用纯SQL，如下所示
SELECT ...
FROM schema1.parcel t
JOIN bounds b
ON st_intersects(t.geom,st_transform(b.geom,4269))
WHERE 'schema1' = any(array['schema1','schema2'])
UNION
SELECT ...
FROM schema2.parcel t
JOIN bounds b
ON st_intersects(t.geom,st_transform(b.geom,4269))
WHERE 'schema2' = any(array['schema1','schema2'])
UNION
SELECT ...
FROM schema3.parcel t
JOIN bounds b
ON st_intersects(t.geom,st_transform(b.geom,4269))
WHERE 'schema3' = any(array['schema1','schema2'])如果你想以动态方式构建查询，那么你可以在单个查询中实现：
select
string_agg(format($$SELECT * FROM %I.parcel t
JOIN bounds b
ON st_intersects(t.geom,st_transform(b.geom,4269)
$$, shm), e'UNION\n') as sql
FROM unnest(array['schema1','schema2']) as shm;
┌───────────────────────────────────────────────────────┐
│ sql │
├───────────────────────────────────────────────────────┤
│ SELECT * FROM schema1.parcel t ↵│
│ JOIN bounds b ↵│
│ ON st_intersects(t.geom,st_transform(b.geom,4269)↵│
│ UNION ↵│
│ SELECT * FROM schema2.parcel t ↵│
│ JOIN bounds b ↵│
│ ON st_intersects(t.geom,st_transform(b.geom,4269)↵│
│ │
└───────────────────────────────────────────────────────┘使用此查询的结果作为EXECUTE语句的参数。
有几个注解。
您可以使用美元加引号的字符串来保持语法突出显示。比较
select 'select
*
from foo';
select $$select
*
from foo$$;使用带有%I占位符的format()函数作为标识符：
select format('%s %I', 'FooBar', 'FooBar');
┌─────────────────┐
│ format │
├─────────────────┤
│ FooBar "FooBar" │
└─────────────────┘页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/65617257
复制相关文章
相似问题
腾讯云开发者
