How to get a result from dynamic SQL in Postgres?
For a dynamic query you need to use the 'execute' command.
EXECUTE dynamic-query-string INTO target-variable...
The manual page for this is here: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
HTH
![Harsimranjeet Singh](https://lh4.googleusercontent.com/-VU7LSCXpP4Q/AAAAAAAAAAI/AAAAAAAAABU/I-iR8BqVk_I/photo.jpg?sz=256)
Harsimranjeet Singh
Working as BI Analyst IN India at Classicinformatics.
Updated on June 28, 2022Comments
-
Harsimranjeet Singh about 2 years
Raw Table for which rule are stored in one table named md_formula , which are used to map in destination table
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'
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'
Get the data from raw table stored in md_Formula Via
TSQL
(Only One Column Selected for Example)This will actually execute
SELECT accountnumber FROM raw_dbs_transactiondetailscash
and get set of data from
raw_dbs_transactiondetailscash
tableDECLARE @sql VARCHAR(100) SELECT TOP 1 @sql= 'SELECT '+Col_src+ ' FROM '+tbl_Src FROM MD_Formula EXEC (@sql)
Via
Postgres
(Dynamic Query is prepared only, how to get data from raw table in dynamic sql is still a question)This need to execute
SELECT accountnumber,referencecurrency,transactiondate FROM raw_dbs_transactiondetailscash
and get result
SELECT 'SELECT '|| string_Agg(col_src,',') ||' FROM ' || tbl_src FROM md_formula WHERE format='Dbs' GROUP BY tbl_src