如何从Postgres中的动态SQL获取结果？ [英] How to get a result from dynamic SQL in Postgres?

问题描述

原始表，规则存储在一个名为md_formula的表中，该表用于映射到目标表中

Raw Table for which rule are stored in one table named md_formula , which are used to map in destination table

为 raw_dbs_transactiondetailscash ：

Drop/Create/Insert for raw_dbs_transactiondetailscash:

DROP TABLE raw_dbs_transactiondetailscash

CREATE TABLE raw_dbs_transactiondetailscash(
    accountnumber VARCHAR(100),
    referencecurrency VARCHAR(100),
    transactiondate datetime)

INSERT INTO raw_dbs_transactiondetailscash(
    accountnumber, referencecurrency, transactiondate)
    SELECT 'XYZ','$','01/01/2016'            

为 md_formula 拖放/创建/插入：

Drop/Create/Insert for md_formula:

DROP TABLE MD_Formula 

CREATE TABLE MD_Formula (
    Format VARCHAR(20),
    tbl_Src VARCHAR(200),
    Col_src VARCHAR(500),
    tbl_Des VARCHAR(200),
    Col_des VARCHAR(100),
    Condition VARCHAR(500) )

INSERT INTO md_formula(format, tbl_src, Col_src, tbl_des,Col_des)    
    SELECT 'Dbs','raw_dbs_transactiondetailscash','accountnumber',
            'normalized_transaction','account_number'
    UNION ALL
    SELECT 'Dbs','raw_dbs_transactiondetailscash','referencecurrency',
            'normalized_transaction','currency'
    UNION ALL
    SELECT 'Dbs','raw_dbs_transactiondetailscash','transactiondate',
            'normalized_transaction','trade_date'

从存储的原始表中获取数据在md_Formula
中通过 TSQL （仅选择一列作为示例）

Get the data from raw table stored in md_Formula Via TSQL (Only One Column Selected for Example)

这实际上将执行

SELECT accountnumber
FROM raw_dbs_transactiondetailscash

并从 raw_dbs_transactiondetailscash 表中获取数据集

DECLARE @sql VARCHAR(100)

SELECT TOP 1 @sql= 'SELECT '+Col_src+ ' FROM '+tbl_Src FROM MD_Formula

EXEC (@sql)

通过 Postgres （仅准备动态查询，如何在动态sql中从原始表中获取数据仍然是一个问题）

Via Postgres (Dynamic Query is prepared only, how to get data from raw table in dynamic sql is still a question)

需要执行

SELECT accountnumber,referencecurrency,transactiondate
FROM raw_dbs_transactiondetailscash

并获得结果

SELECT 'SELECT '|| string_Agg(col_src,',') ||' FROM ' ||  tbl_src FROM md_formula
WHERE format='Dbs'
GROUP BY tbl_src

推荐答案

对于动态查询，您需要使用执行命令。

For a dynamic query you need to use the 'execute' command.

EXECUTE dynamic-query-string INTO target-variable...

此处的手册页为： http://www.postgresql.org/docs/ current / static / plpgsql-statements.html＃PLPGSQL-STATEMENTS-EXECUTING-DYN

HTH

这篇关于如何从Postgres中的动态SQL获取结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！