Re: Text parameter is treated as sql query in postgresql function

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>

--