Postgresql – Are there any way to execute a query inside a string value in PostgreSQL

pivotpostgresql

I wanted to pivot a table using cross tab function which have dynamic number of category names. By digging out the web I have found a solution.

Solution – http://www.rummandba.com/2013/03/postgresql-dynamic-crosstab-function.html

Function Used 

   DROP FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text, category_sql 
   text, v_matrix_col_type varchar(100), v_matrix_rows_name_and_type 
   varchar(100),debug bool);
   CREATE OR REPLACE FUNCTION crosstab_dynamic_sql_gen_fn (source_sql text, 
   category_sql text, v_matrix_col_type varchar(100), 
   v_matrix_rows_name_and_type varchar(100),debug bool default false)
   RETURNS text AS $$
   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;
   $$ language 'plpgsql';

Query used to get the result of the function 

 select crosstab_dynamic_sql_gen_fn('select emp_id, month, qty from sales 
 order by 1','select distinct month from sales','int','year text');

The result of the query is string. Can somebody tell me how to execute the query inside the string. Below is the result of query

enter image description here

Best Answer

You pull it down to the client, and send it back to the server. That makes this a really bad idea. Even if you can dynamically generate the types of the return column in a string, which is proven here, you can't dynamically return anything with standards-compliant SQL.

If you're using psql, there is a command to automate that processes. After something returns a string of sql, you can run it with \gexec

As they say in the source above,

As long as you get the generated sql, you may do whatever you like with that. You may create function, view etc.

So you can do that kind of stuff too. That is, things that don't return a dynamic list of columns, like issue a CREATE FUNCTION or a CREATE SQL command.

Related Question