How to get a result from dynamic SQL in Postgres?

Question

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 table

DECLARE @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

Diplodocus

Please post your table definition as formatted text, not links to screenshots. You also need to add the expected output of the query, I for one have no idea what you are trying to achieve – Furl

Answer 1

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

Lavine

The Dynamic Query prepared by the above query is a result with set of rows which i feel can not be assigned to variable. – Diplodocus

@HarsimranjeetSingh that can be assigned too, you could use multiple variables, record or a row-typed variable to do that (in case of fix number of columns). The only part, which can be hard is reading from a general record type. But you can use json or hstore for a more general solution (in case of varying column numbers / types). – Melodymeloid

@Melodymeloid below is the query so far accomplised but this is throwing error .I want to return record type variable at the EOD. CREATE OR REPLACE FUNCTION getrowdata1() RETURNS setof record AS $func$ DECLARE srowdata record; sql VARCHAR(100); BEGIN /*sql:=(SELECT 'SELECT '|| string_Agg(col_src,',') ||' FROM ' || tbl_src FROM md_formula WHERE format='Dbs' GROUP BY tbl_src);*/ sql:= (SELECT 'SELECT * FROM md_formula'); EXECUTE(sql) INTO srowdata; RETURN srowdata; END $func$ LANGUAGE plpgsql; – Diplodocus

@HarsimranjeetSingh you cannot use return if your function is defined as returning a 'setof'. As you only want to return a single result, remove the 'setof' keyword from the function definition. – Lavine

I've tried the same and when used SELECT getrowdata1() the result is coming as a weird text . like ("MDXCDSD1 (...) – Diplodocus

How do you mean 'weird text' ? Does it contain the values you'd expect to see in a strange format or just gibberish? – Lavine

Answer 2

So this is what i've tried to attained result as per my requirement. Thanks to @pozs your link to some post was really helpful, Appreciated.

Solution:

Create or replace Function gen_Test(query_name refcursor)
returns refcursor
as $$
Declare sql text;
begin
sql:=(SELECT 'SELECT '|| string_Agg(col_src,',') ||' FROM ' ||  tbl_src FROM md_formula
WHERE format='Dbs'
GROUP BY tbl_src);
open query_name for execute 
sql;
return query_name;
end;
$$ language plpgsql;


select gen_Test('english');
fetch all in english;

PS: Appreciated everyone feedback for giving time for this issue.

Diplodocus

Can anyone tell me how to insert from this into table. posted same question at link – Diplodocus