PostgreSQL DBLink where语句使用本地数据库中的变量

ruarlubt 于 2023-06-22 发布在 PostgreSQL

关注(0)|答案(1)|浏览(280)

我有两个PostgreSQL数据库（一个源数据库和一个目标数据库），其中包含多个表。我设法使用DBLink将数据从源传输到目标（复制数据或复制数据子集）。
我的问题是如何使语句更优雅，因为它是动态生成的。例如，目前我有以下声明：

DO $$
BEGIN
DECLARE
    upsert_value Timestamp;
BEGIN
timestamp_value := ISNULL((SELECT "timestamp" FROM system.lastupdatetable_stg WHERE "group_id" = 54 AND "tenant_id" = 23 AND "table_id" = 3327 LIMIT 1), to_timestamp(0));
INSERT INTO public."AccessToDivisions"
("CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId")
SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId"
FROM dblink('<connection>',
'SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId" FROM public."AccessToDivisions" t
WHERE 1=1 
AND "ModifiedAt" >' + timestamp_value
) AS rt("CreatedAt" timestamp without time zone, "Default" boolean, "Division" integer, "Employee" character(36), "GCRecord" integer, "ModifiedAt" timestamp without time zone, "OID" integer, "OptimisticLockField" integer, "TenantId" character(36))
ON CONFLICT("TenantId", "OID")
DO UPDATE SET "CreatedAt" = excluded."CreatedAt", "Default" = excluded."Default", "Division" = excluded."Division", "Employee" = excluded."Employee", "GCRecord" = excluded."GCRecord", "ModifiedAt" = excluded."ModifiedAt", "OptimisticLockField" = excluded."OptimisticLockField";
INSERT INTO system.lastupdatetable_stg ("group_id", "tenant_id", "table_id", "snapshot", "timestamp") VALUES (54, 23, 3327, false, (SELECT MAX("ModifiedAt") FROM public."AccessToDivisions")) ON CONFLICT ("group_id", "tenant_id", "table_id") DO UPDATE SET "timestamp" = excluded."timestamp";
END;
END $$;

system.lastupdatetable_stg驻留在目标数据库中。是否可以不声明变量upsert_value，而是在dblink语句中进行调用，如下所示？

DO $$
BEGIN
INSERT INTO public."AccessToDivisions"
("CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId")
SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId"
FROM dblink('<connection>',
'SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId" FROM public."AccessToDivisions" t
WHERE 1=1 
AND "ModifiedAt" > ISNULL((SELECT "timestamp" FROM system.lastupdatetable_stg WHERE "group_id" = 54 AND "tenant_id" = 23 AND "table_id" = 3327 LIMIT 1), to_timestamp(0))'
) AS rt("CreatedAt" timestamp without time zone, "Default" boolean, "Division" integer, "Employee" character(36), "GCRecord" integer, "ModifiedAt" timestamp without time zone, "OID" integer, "OptimisticLockField" integer, "TenantId" character(36))
ON CONFLICT("TenantId", "OID")
DO UPDATE SET "CreatedAt" = excluded."CreatedAt", "Default" = excluded."Default", "Division" = excluded."Division", "Employee" = excluded."Employee", "GCRecord" = excluded."GCRecord", "ModifiedAt" = excluded."ModifiedAt", "OptimisticLockField" = excluded."OptimisticLockField";
INSERT INTO system.lastupdatetable_stg ("group_id", "tenant_id", "table_id", "snapshot", "timestamp") VALUES (54, 23, 3327, false, (SELECT MAX("ModifiedAt") FROM public."AccessToDivisions")) ON CONFLICT ("group_id", "tenant_id", "table_id") DO UPDATE SET "timestamp" = excluded."timestamp";
END $$;

第二个查询的问题是，在查找源数据库时，找不到system.lastupdatetable_stg。
有没有办法让select语句执行到destination，或者有办法在dbink内部传递参数，而不是使用引号进行简单的字符串插值？

postgresql

来源：https://stackoverflow.com/questions/76422472/postgresql-dblink-where-statement-using-variable-from-local-database

1条答案

按热度按时间

fcwjkofz1#

似乎没有办法在dblink内部执行命令，因为dblink在源上执行时会从目的地获得一些东西。我认为@Frank Heiken注解部分解决了这个问题，在源数据库上使用外来表，但同时增加了复杂性，如果你必须处理数百个数据库。
我能够使用格式和%L参数作为引号插值的替代，使SQL语句变得更漂亮，这将简化更高级别的代码（C#），同时生成以下代码：

DO $$
BEGIN
DECLARE
    upsert_value Timestamp;
BEGIN
timestamp_value := ISNULL((SELECT "timestamp" FROM system.lastupdatetable_stg WHERE "group_id" = 54 AND "tenant_id" = 23 AND "table_id" = 3327 LIMIT 1), to_timestamp(0));
INSERT INTO public."AccessToDivisions"
("CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId")
SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId"
FROM dblink('<connection>',
format('SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId" FROM public."AccessToDivisions" t
WHERE 1=1 
AND "ModifiedAt" > %L', timestamp_value)
) AS rt("CreatedAt" timestamp without time zone, "Default" boolean, "Division" integer, "Employee" character(36), "GCRecord" integer, "ModifiedAt" timestamp without time zone, "OID" integer, "OptimisticLockField" integer, "TenantId" character(36))
ON CONFLICT("TenantId", "OID")
DO UPDATE SET "CreatedAt" = excluded."CreatedAt", "Default" = excluded."Default", "Division" = excluded."Division", "Employee" = excluded."Employee", "GCRecord" = excluded."GCRecord", "ModifiedAt" = excluded."ModifiedAt", "OptimisticLockField" = excluded."OptimisticLockField";
INSERT INTO system.lastupdatetable_stg ("group_id", "tenant_id", "table_id", "snapshot", "timestamp") VALUES (54, 23, 3327, false, (SELECT MAX("ModifiedAt") FROM public."AccessToDivisions")) ON CONFLICT ("group_id", "tenant_id", "table_id") DO UPDATE SET "timestamp" = excluded."timestamp";
END;
END $$;

所以，最后似乎格式在DBLink中也起作用，作为例外，这种方式在C#端可以是：
对于满载：

FROM dblink('<connection>',
'SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId" FROM public."AccessToDivisions" t
WHERE 1=1

对于增量载荷：

FROM dblink('<connection>',
format('SELECT "CreatedAt", "Default", "Division", "Employee", "GCRecord", "ModifiedAt", "OID", "OptimisticLockField", "TenantId" FROM public."AccessToDivisions" t
WHERE 1=1 
AND "ModifiedAt" > %L', timestamp_value)

展开查看全部

赞(0）回复(0）举报 2023-06-22

我来回答

PostgreSQL DBLink where语句使用本地数据库中的变量

1条答案

相关问题

热门标签

最新问答