动态执行

teledb=# CREATE OR REPLACE PROCEDURE p_execute() AS
$$  
DECLARE
    v_sql TEXT;
    v_relname TEXT;
BEGIN      
    v_sql := 'SELECT relname FROM pg_class limit 1';
    EXECUTE v_sql INTO v_relname;   
    RAISE NOTICE 'relname = %',v_relname;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_execute();
NOTICE:  relname = pg_stat_statements
CALL
teledb=#

也可以使用immediate。

teledb=# CREATE OR REPLACE PROCEDURE p_execute() AS
$$  
DECLARE
    v_sql TEXT;
    v_relname TEXT;
BEGIN      
    v_sql := 'SELECT relname FROM pg_class limit 1';
    EXECUTE immediate v_sql INTO v_relname;   
    RAISE NOTICE 'relname = %',v_relname;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_execute();
NOTICE:  relname = s1
CALL
teledb=#

动态执行就是拼SQL 语句，然后使用 EXECUTE 命令执行。

执行一个没有结果的命令

teledb=# CREATE OR REPLACE PROCEDURE p_perform()  AS
$$   
BEGIN      
    perform md5(random()::text);
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# call p_perform();
CALL
teledb=#  

获取执行结果

teledb=# CREATE OR REPLACE PROCEDURE p_found()  AS
$$  
DECLARE
    v_relname TEXT; 
BEGIN      
    SELECT relname INTO v_relname FROM pg_class limit 1;
    IF FOUND THEN
        RAISE NOTICE '查询到记录，值为%',v_relname;    
    ELSE
        RAISE NOTICE '查不到记录' ;
    END IF;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# CALL p_found();
NOTICE:  查询到记录，值为pg_stat_statements
CALL

获取影响行数

teledb=# CREATE OR REPLACE PROCEDURE p_row_count()  AS
$$  
DECLARE  
    v_row_count BIGINT;
BEGIN        
    delete from t1;
    GET DIAGNOSTICS v_row_count = ROW_COUNT;
    RAISE NOTICE '查询到的记录数为 % ',v_row_count;
END;
$$
LANGUAGE plpgsql;
CREATE PROCEDURE
teledb=# call p_row_count();
NOTICE:  查询到的记录数为 3 
CALL
teledb=#