Обсуждение: Text parameter is treated as sql query in postgresql function
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
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;
$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.
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.
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
Вложения
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.klaver@aklaver.com
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.klaver@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.klaver@aklaver.com
On 12/01/2016 17:07, Yash Gajbhiye wrote:
> 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 <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
I missed whatever passed upthread, but at a guess I'd say all the
quoting is causing problems here. Why not use the quote_ident() and
quote_literal() functions? By the same token, I don't think you need to
put quotation marks around the parameters.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
On 01/12/2016 09:07 AM, Yash Gajbhiye wrote: > 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: > > > 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 <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; > > > 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. See Raymonds post. It also alright to use dollar quoting outside a function: http://www.postgresql.org/docs/9.4/static/sql-syntax-lexical.html 4.1.2.4. Dollar-quoted String Constants That would also eliminate the escaping you have to do in the passed in string. That is what is causing the below, the ''N'' should be 'N'. > > > 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 <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)" > PL/pgSQL function leavetypeaccrual(refcursor,text,text,text) line 5 at > SQL statement > > > Thanks > Yash. > > -- Adrian Klaver adrian.klaver@aklaver.com