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

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