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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
