Running a dynamic query crosstab

Question

I implemented this function in my Postgres database: http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/

Here's the function:

create or replace function xtab (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns varchar language plpgsql as $$ declare dynsql1 varchar; dynsql2 varchar; columnlist varchar; begin -- 1. retrieve list of column names. dynsql1 = 'select string_agg(distinct '||colc||'||'' '||celldatatype||''','','' order by '||colc||'||'' '||celldatatype||''') from '||tablename||';'; execute dynsql1 into columnlist; -- 2. set up the crosstab query dynsql2 = 'select * from crosstab ( ''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2'', ''select distinct '||colc||' from '||tablename||' order by 1'' ) as ct ( '||rowc||' varchar,'||columnlist||' );'; return dynsql2; end $$; 

So now I can call the function:

 select xtab('globalpayments','month','currency','(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2)','text'); 

Which returns (since the return type of the function is varchar):

 select * from crosstab ( 'select month,currency,(sum(total_fees)/sum(txn_amount)*100)::decimal(48,2) from globalpayments group by 1,2 order by 1,2' , 'select distinct currency from globalpayments order by 1' ) as ct ( month varchar,CAD text,EUR text,GBP text,USD text ); 

How can I make this function not only generate dynamic crosstab code, but also execute the result? I., the result is when I manually copy / paste / execute this. But I want it to be executed without this extra step: the function must collect a dynamic request and execute it:

Change 1

This function is approaching, but I need it to return more than just the first column of the first record

Taken from: Is there a way to execute a query inside a string value (e.g. eval) in PostgreSQL?

 create or replace function eval( sql text ) returns text as $$ declare as_txt text; begin if sql is null then return null ; end if ; execute sql into as_txt ; return as_txt ; end; $$ language plpgsql 

usage: select * from eval($$select * from analytics limit 1$$)

However, it simply returns the first column of the first record:

 eval ---- 2015 

when the actual result is as follows:

 Year, Month, Date, TPV_USD ---- ----- ------ -------- 2016, 3, 2016-03-31, 100000 

Answer 1

What you ask for is impossible . SQL is a strongly typed language. PostgreSQL functions must declare a return type ( RETURNS .. ) at creation time.

The limited way around this is with polymorphic functions. If you can specify the type of return during the function call . But this is not obvious from your question.

• Refactoring the PL / pgSQL function to return the output of various SELECT queries

You can return a fully dynamic result with anonymous entries. But then you need to provide a list of column definitions for each call. And how do you know about returned columns? Catch 22.

There are various workarounds, depending on what you need or with which you can work. Since all data columns seem to have the same data type, I suggest returning an array : text[] . Or you can return a document type like hstore or json . Connected:

• A dynamic alternative for turning with CASE and GROUP BY

• Dynamically convert hstore keys to columns for an unknown key set

But it would be easier to just use two calls: 1: Let Postgres build the request. 2: Executing and getting returned rows.

• Select multiple max () values ​​using a single SQL statement

---

I would not use the function from Eric Minikel, as presented in your question in general. It is not safe for SQL injection using maliciously misrepresented identifiers. Use format() to build query strings unless you are using an older version older than Postgres 9.1.

A shorter and cleaner implementation might look like this:

 CREATE OR REPLACE FUNCTION xtab(_tbl regclass, _row text, _cat text , _expr text -- still vulnerable to SQL injection! , _type regtype) RETURNS text AS $func$ DECLARE _cat_list text; _col_list text; BEGIN -- generate categories for xtab param and col definition list EXECUTE format( $$SELECT string_agg(quote_literal(x.cat), '), (') , string_agg(quote_ident (x.cat), %L) FROM (SELECT DISTINCT %I AS cat FROM %s ORDER BY 1) x$$ , ' ' || _type || ', ', _cat, _tbl) INTO _cat_list, _col_list; -- generate query string RETURN format( 'SELECT * FROM crosstab( $q$SELECT %I, %I, %s FROM %I GROUP BY 1, 2 -- only works if the 3rd column is an aggregate expression ORDER BY 1, 2$q$ , $c$VALUES (%5$s)$c$ ) ct(%1$I text, %6$s %7$s)' , _row, _cat, _expr -- expr must be an aggregate expression! , _tbl, _cat_list, _col_list, _type ); END $func$ LANGUAGE plpgsql; 

The same function call as the original version. The crosstab() function is provided by the optional tablefunc module, which must be installed. The basics:

• PostgreSQL crosstab query

It safely handles column and table names. Pay attention to the use of object identifier types regclass and regtype . Also works for names matching schema criteria.

• Table name as parameter of PostgreSQL function

However, it is not completely safe as long as you pass a string that will be executed as an expression ( _expr - cellc in the original request). This type of input is inherently unsafe for SQL injection and should never be exposed to the general public.

• SQL Injection in Postgres Functions and Prepared Queries

It scans the table only once for both category lists and should be slightly faster.

It is still impossible to return fully dynamic row types, as this is strictly impossible.

Answer 2

Not entirely impossible, you can still execute it (from the query, execute a string and return SETOF RECORD.

Then you need to specify the format of the return record. The reason in this case is that the planner must know the return format before he can make certain decisions (materialization comes to mind).

So, in this case you have to FOLLOW the query, return the rows and return SETOF RECORD.

For example, we could do something similar using a wrapper function, but the same logic can be added to your function:

 CREATE OR REPLACE FUNCTION crosstab_wrapper (tablename varchar, rowc varchar, colc varchar, cellc varchar, celldatatype varchar) returns setof record language plpgsql as $$ DECLARE outrow record; BEGIN FOR outrow IN EXECUTE xtab($1, $2, $3, $4, $5) LOOP RETURN NEXT outrow END LOOP; END; $$; 

Then you create a record structure when you call the function, just like a crosstab. Then, when you ask for everything, you will need to create a record structure (like (type col1, type col2, etc.), how do you do with the connection.