时间戳

首页 > 解决方案 > PL/pgsql 在查询中动态转换 regclass 的正确方法

postgresql - PL/pgsql 在查询中动态转换 regclass 的正确方法

问题描述

我有一个函数将遍历一个表，然后遍历从外部循环中的每个表继承的所有表。我发现了一个很棒的函数来查找从父表继承的表。我的问题是，如何动态更改表名以查找所有父级？所以'shopmaster.pb'::regclass 会类似于'shopmaster.'||i.tablename::regClass。

CREATE OR REPLACE FUNCTION shopmaster.cascade_filters()
RETURNS jsonb AS $$
DECLARE
    i record;
    k shopmaster."catalog_filters";
BEGIN
    DELETE FROM shopmaster.catalog_filters WHERE isparent=false;
    FOR i IN SELECT shopmaster.catalog.catalogid,columnname,columntype,columnnvalues,
        tablename FROM shopmaster.catalog_filters INNER JOIN catalog ON 
        (catalog_filters.catalogid=catalog.catalogid) WHERE isparent=true LOOP
        FOR k IN  
        WITH RECURSIVE inh AS (
            SELECT i.inhrelid FROM pg_catalog.pg_inherits i WHERE inhparent=
            'shopmaster.pb'::regclass
            UNION
            SELECT i.inhrelid FROM inh INNER JOIN pg_catalog.pg_inherits i ON
            (inh.inhrelid = i.inhparent)
        )
        SELECT pg_namespace.nspname, pg_class.relname 
            FROM inh 
            INNER JOIN pg_catalog.pg_class ON (inh.inhrelid=pg_class.oid) 
            INNER JOIN pg_catalog.pg_namespace ON
            (pg_class.relnamespace=pg_namespace.oid) LOOP

        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

标签： postgresqlplpgsql

解决方案

原来有一个简单的方法。执行一个子表达式来连接或获取表标识符，然后将其转换为 ::regclass。例如('shopmaster.'||i.tablename)::regclass

CREATE OR REPLACE FUNCTION shopmaster.cascade_filters()
RETURNS jsonb AS $$
DECLARE
    i record;
    k record;
BEGIN
    DELETE FROM shopmaster.catalog_filters WHERE isparent=false;
    FOR i IN SELECT shopmaster.catalog.catalogid,columnname,columntype,
        columnnvalues,tablename
        FROM shopmaster.catalog_filters INNER JOIN shopmaster.catalog ON
        (catalog_filters.catalogid=catalog.catalogid) WHERE isparent=true LOOP
        FOR k IN
        WITH RECURSIVE inh AS (
            SELECT ih.inhrelid FROM pg_catalog.pg_inherits ih WHERE inhparent=
            ('shopmaster.'||i.tablename)::regclass
            UNION
            SELECT ih.inhrelid FROM inh INNER JOIN pg_catalog.pg_inherits ih ON
            (inh.inhrelid = ih.inhparent)
        )
        SELECT pg_namespace.nspname, pg_class.relname,
            shopmaster.catalog.catalogid
            FROM inh
            INNER JOIN pg_catalog.pg_class ON (inh.inhrelid=pg_class.oid)
            INNER JOIN pg_catalog.pg_namespace ON
            (pg_class.relnamespace=pg_namespace.oid)
            INNER JOIN shopmaster.catalog ON
            (pg_class.relname=shopmaster.catalog.tablename) LOOP
            EXECUTE 'INSERT INTO shopmaster.catalog_filters (catalogid,'
                ||'columnname,columntype,columnnvalues,isparent,owner) '
                ||'VALUES($1,$2,$3,$4,false,$5)' USING k.catalogid,i.columnname,
                i.columntype,i.columnnvalues,i.catalogid;
        END LOOP;
    END LOOP;
    RETURN jsonb_build_object('ok',true);
END;
$$ LANGUAGE plpgsql;

推荐阅读