current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.6 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 0 down vote favorite

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
share|improve this question
    
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 – a_horse_with_no_name Dec 7 '15 at 12:16
up vote 1 down vote

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

share|improve this answer
    
The Dynamic Query prepared by the above query is a result with set of rows which i feel can not be assigned to variable. – Harsimranjeet Singh Dec 7 '15 at 12:27
1  
@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). – pozs Dec 7 '15 at 12:56
    
@pozs 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; – Harsimranjeet Singh Dec 8 '15 at 8:55
    
@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. – Graeme Dec 8 '15 at 9:47
    
I've tried the same and when used SELECT getrowdata1() the result is coming as a weird text . like ("MDXCDSD1 (...) – Harsimranjeet Singh Dec 8 '15 at 13:03
up vote 0 down vote

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.

share|improve this answer
    
Can anyone tell me how to insert from this into table. posted same question at link – Harsimranjeet Singh Dec 9 '15 at 7:05

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.