From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Yash Gajbhiye <yashg(at)timeforge(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Text parameter is treated as sql query in postgresql function |
Date: | 2016-01-12 17:29:20 |
Message-ID: | 569537F0.1080904@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ted Toth | 2016-01-12 17:41:22 | postgres user with password read-only user without? |
Previous Message | Raymond O'Donnell | 2016-01-12 17:16:32 | Re: Text parameter is treated as sql query in postgresql function |