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

On 12/01/2016 17:07, 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:
>
> CREATE OR REPLACE FUNCTION dynamic_crosstab(
> source_sql text,
> category_sql text,
> v_matrix_col_type text,
> v_matrix_rows_name_and_type text,
> debug boolean DEFAULT false)
> RETURNS text AS
> $BODY$
> DECLARE
> v_sql text;
> curs1 refcursor;
> v_val text;
> BEGIN
> v_sql = v_matrix_rows_name_and_type;
> OPEN curs1 FOR execute category_sql;
> Loop
> FETCH curs1 INTO v_val;
> exit when v_val IS NULL;
> v_sql = v_sql ||' , "'||v_val||'" '||v_matrix_col_type;
> IF debug THEN
> RAISE NOTICE 'v_val = %',v_val;
> END IF;
> END LOOP;
> CLOSE curs1;
> v_sql := 'SELECT * from crosstab(' || chr(10) || E' \''||source_sql ||
> E'\','||chr(10) || E' \''||category_sql || E'\'' || chr(10)|| ' ) AS ('
> || v_sql ||')';
> IF debug THEN
> RAISE NOTICE 'v_sql = %',v_sql;
> END IF;
> RETURN v_sql;
> END;
>
> 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

I missed whatever passed upthread, but at a guess I'd say all the
quoting is causing problems here. Why not use the quote_ident() and
quote_literal() functions? By the same token, I don't think you need to
put quotation marks around the parameters.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod(at)iol(dot)ie