时间戳

首页 > 解决方案 > postgres中的动态选择功能

sql - postgres中的动态选择功能

问题描述

我将如何编写和实现一个函数，该函数根据传递给函数的参数+其他变量动态地从多个表中进行选择。选择的数据需要由函数返回。

到目前为止，我已经尝试了 PERFORM ，这很难获得回报，还尝试了 EXECUTE ，它似乎只返回 INTO 变量而不是集合。https://www.postgresql.org/docs/9.1/plpgsql-declarations.html。我几乎可以肯定这不是设计问题，因为这可以在 TSQL spocs 中的几行中完成。

标签： sqlpostgresql

解决方案

该方法的问题是找到一种方法来存储变量并在执行时返回所述变量的结果。这下面的技巧和参数的传递很简单。

CREATE OR REPLACE FUNCTION foo( 
) 
RETURNS TABLE ( 
     key TEXT 
    , value GEOMETRY
) 
LANGUAGE 'plpgsql'  
SECURITY INVOKER 
AS $$ 
DECLARE 
    err1 TEXT;
    err2 TEXT;
    err3 TEXT ; 
    dyn_sql TEXT ; 
    
BEGIN 
    
 
    SELECT 
        concat(LEFT(concat( 'SELECT q.viewn, q.geom /* INTO constraint_output_temp */ FROM ( ' , string_agg(sql_parts, '') ), -11) , ') AS q' ) AS query
    INTO 
        dyn_sql
    FROM    
        (
            SELECT
                CASE
                    WHEN 1 = 1
                     THEN  concat( 'SELECT ''', t.matviewname, ''' as viewn, ', t.matviewname, '.geom FROM ', t.schemaname, '.', t.matviewname, ' AS ', t.matviewname, ' INNER JOIN targets ON st_intersects(', t.matviewname, '.geom, targets.geom) UNION ALL ' )
                     ELSE NULL
                END AS sql_parts
                , concat(t.schemaname, '.', t.matviewname) AS obj
                , t.matviewname AS viewn
            FROM
                pg_catalog.pg_matviews AS t
            WHERE
                schemaname = 'something'     
        ) AS q
    ;
    
    RETURN QUERY EXECUTE dyn_sql ; 

    EXCEPTION
    WHEN OTHERS THEN GET STACKED DIAGNOSTICS 
        err1 = MESSAGE_TEXT
        , err2 = PG_EXCEPTION_DETAIL
        , err3 = PG_EXCEPTION_HINT ;
    
    RETURN QUERY SELECT CONCAT( err1, err2, err3 )::TEXT ;
    
END $$ ;

SELECT * FROM foo(  ) ;

推荐阅读