Re: Text parameter is treated as sql query in postgresql function - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Text parameter is treated as sql query in postgresql function |
Date | |
Msg-id | 569537F0.1080904@aklaver.com Whole thread Raw |
In response to | Re: Text parameter is treated as sql query in postgresql function (Yash Gajbhiye <yashg@timeforge.com>) |
List | 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.klaver@aklaver.com
pgsql-general by date: