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