Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxx


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux