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

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 — user330315
– user330315, Commented Dec 7, 2015 at 12:16

John Smith · Accepted Answer · 2023-01-11 23:57:09Z

12

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

edited Jan 11, 2023 at 23:57

John Smith

7,4477 gold badges52 silver badges63 bronze badges

answered Dec 7, 2015 at 12:16

Graeme

1,70816 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Harsimranjeet Singh Over a year ago

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

pozs Over a year ago

@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).

Harsimranjeet Singh Over a year ago

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

Graeme Over a year ago

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

Harsimranjeet Singh Over a year ago

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

|

Harsimranjeet Singh · Accepted Answer · 2015-12-08 13:14:40Z

0

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.

answered Dec 8, 2015 at 13:14

Harsimranjeet Singh

5242 gold badges6 silver badges19 bronze badges

1 Comment

Harsimranjeet Singh Over a year ago

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

Collectives™ on Stack Overflow

How to get a result from dynamic SQL in Postgres?

2 Answers 2

6 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related