Hello Adrian, Thank you for your response. Sorry about the typos in the previous post.
I will try to explain myself more clearly. This is my first function to create a dynamic query and it is as follows: CREATE OR REPLACE FUNCTION dynamic_crosstab( source_sql text, category_sql text, v_matrix_col_type text, v_matrix_rows_name_and_type text, debug boolean DEFAULT false) RETURNS text AS $BODY$ DECLARE v_sql text; curs1 refcursor; v_val text; BEGIN v_sql = v_matrix_rows_name_and_type; OPEN curs1 FOR execute category_sql; Loop FETCH curs1 INTO v_val; exit when v_val IS NULL; v_sql = v_sql ||' , "'||v_val||'" '||v_matrix_col_type; IF debug THEN RAISE NOTICE 'v_val = %',v_val; END IF; END LOOP; CLOSE curs1; v_sql := 'SELECT * from crosstab(' || chr(10) || E' \''||source_sql || E'\','||chr(10) || E' \''||category_sql || E'\'' || chr(10)|| ' ) AS (' || v_sql ||')'; IF debug THEN RAISE NOTICE 'v_sql = %',v_sql; END IF; RETURN v_sql; END; This works fine. It accepts 2 sql queries and other parameters as inputs and output is a sql query which looks like this: SELECT * from crosstab( sql query 1, sql query 2) AS (....); and this query works fine too. I want to execute and return rows from this query. Hence I am using another function to accomplish, which is : CREATE OR REPLACE FUNCTION leavetypeaccrual( cur refcursor, text, text, text) RETURNS SETOF refcursor AS $BODY$ declare val_1 text; begin select * from dynamic_crosstab( 'select p.location_id, p.employee_id, pt.description, sum(p.hours_allocated) as hours_allocated from preference_type pt, preference p, preference_date_etl pde, date_etl de where pt.id = p.preference_type_id and pde.preference_id = p.id and pde.corporation_id = $4 and de.id = pde.date_etl_id and pde.deleted = ''''N'''' and p.deleted = ''''N'''' and pt.deleted = ''''N'''' and de.local_date between ''''$2'''' and ''''$3'''' and p.employee_id IN ( select id from employee where user_id IN ( select id from app_user where corporation_id =$4)) group by p.location_id, p.employee_id, pt.description ', ' select distinct description from preference_type where deleted =''''N'''' and corporation_id=' || $4, 'text','location_id int , employee_id int',false) into val_1; open cur for execute val_1; return next cur; end; Now the first input parameter for my select * from dynamic_crosstab(...) is treated as a string input , but the second input parameter (' select distinct description from preference_type.....) is treated as a seperate sql query instead of string because of the ''''N''''. I need to use deleted='''''N'''' the same way I have used in first input parameter. Please advice how I can achieve this. Error Message: ERROR: syntax error at or near "N" LINE 1: ...description from preference_type where deleted =''N'' and co... ^ QUERY: select distinct description from preference_type where deleted =''N'' and corporation_id=43340 CONTEXT: PL/pgSQL function dynamic_crosstab(text,text,text,text,boolean) line 8 at OPEN SQL statement "select * from dynamic_crosstab(' select p.location_id, p.employee_id, pt.description, sum(p.hours_allocated) as hours_allocated from preference_type pt, preference p, preference_date_etl pde, date_etl de where pt.id = p.preference_type_id and pde.preference_id = p.id and pde.corporation_id = $4 and de.id = pde.date_etl_id and pde.deleted = ''''N'''' and p.deleted = ''''N'''' and pt.deleted = ''''N'''' and de.local_date between ''''$2'''' and ''''$3'''' and p.employee_id IN ( select id from employee where user_id IN ( select id from app_user where corporation_id =$4)) group by p.location_id, p.employee_id, pt.description ', ' select distinct description from preference_type where deleted =''''N'''' and corporation_id=' || $4, 'text','location_id int , employee_id int',false)" PL/pgSQL function leavetypeaccrual(refcursor,text,text,text) line 5 at SQL statement Thanks Yash. On Tue, Jan 12, 2016 at 9:44 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 01/11/2016 11:47 PM, Yash Gajbhiye wrote: > >> I am using postgres crosstab() function to create a table. >> >> My first dynamic query function (dynamic_crosstab) creates a sql select >> statement containing crosstab(), and then this select statement gives >> the final result on execution. /*dynamic_crosstab functions works >> perfectly*/ >> >> I need to execute this select query (result of dynamic_crosstab >> function) by using parameters, so I am again using a function as follows. >> >> >> CREATE OR REPLACE FUNCTION leavetypeaccrual( >> >> cur refcursor, >> >> text, >> >> text, >> >> text) >> >> RETURNS SETOF refcursor AS >> >> $BODY$ >> >> declare >> >> val_1 text; >> >> begin >> >> select * from dynamic_crosstab($ select >> >> p.location_id, p.employee_id, pt.description, sum(p.hours_allocated) as >> hours_allocated >> > > I am having a hard figuring out what the above is supposed to be doing, in > particular this?: > > $ select > > p.location_id, p.employee_id, pt.description, sum(p.hours_allocated) as > hours_allocated ... > > Why the leading $? > > Would it be possible to cut and paste the errors in the future, I had to > enlarge the images to get these old eyes to see the issue. At any rate from > what I could see, it is a quoting issue, which I believe is related to the > question above. > > > >> from >> >> preference_type pt, preference p, preference_date_etl pde, date_etl de >> >> where >> >> pt.id <http://pt.id> = p.preference_type_id and >> >> pde.preference_id = p.id <http://p.id> and >> >> pde.corporation_id = $4 and >> >> de.id <http://de.id> = pde.date_etl_id and >> >> pde.deleted = ''N'' and >> >> p.deleted = ''N'' and >> >> pt.deleted = ''N'' and >> >> de.local_date between ''$2'' and ''$3'' and >> >> p.employee_id IN ( >> >> select id from employee where user_id IN ( select id from app_user where >> corporation_id =||$4||)) >> >> group by p.location_id, p.employee_id, pt.description $, >> >> $ select distinct description from preference_type where deleted =''N'' >> and corporation_id=$ || $4, >> >> 'text','location_id int , employee_id int',false) into val_1; >> >> open cur for execute val_1; >> >> return next cur; >> >> end; >> >> $BODY$ >> >> >> Now this function should execute the crosstab() function and it does >> when I use deleted= 'N' in the second parameter but shows error because >> crosstab() needs deleted=''N'' to execute. >> >> Inline image 1 >> >> And I need to use deleted=''N'' to get my results but postgres treats my >> second parameter as a individual query when I try to do it. >> >> Inline image 2 >> >> The first parameter is passed perfectly with deleted =''N'' but >> postgres does not recognize second parameter when deleted=''N''. >> >> Please suggest what modifications I should do to make this work. >> >> >> Thanks. >> >> >> -- >> Yash Gajbhiye >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > --