Note: this plug-in is only demonstrated in the test environment. If it is used in the production environment, please assess the risk yourself

Environment list:

Host 1: intranet: 125.10.1.166

System configuration list:

GPDB configuration list:

★ download the corresponding postgres version

--gpadmin User operation
$> mkdir /home/demo/tablefunc
$> cd /home/demo/tablefunc
$> wget https://ftp.postgresql.org/pub/source/v8.2.15/postgresql-8.2.15.tar.gz

★make

--gpadmin user operation
$> cd /home/demo/tablefunc/postgresql-8.2.15/contrib/tablefunc
$> make USE_PGXS=1 install

★ send tablefunc.so to other gpdb machines

$> gpscp -f all_nomaster  /usr/local/greenplum-db-4.3.25.1/lib/postgresql/tablefunc.so =:/usr/local/greenplum-db-4.3.25.1/lib/postgresql/

$> gpssh -f all_nomaster "chmod 755 /usr/local/greenplum-db-4.3.25.1/lib/postgresql/tablefunc.so"

★ swipe the tablefunc plug-in to the specified database

$> psql -f  /usr/local/greenplum-db-4.3.25.1/share/postgresql/contrib/tablefunc.sql

★ confirm whether to swipe tablefunc successfully

testdb=# \df tablefunc.*                                                                                                                                 
                                              List of functions
  Schema   |    Name     |      Result data type      |             Argument data types             |  Type  
-----------+-------------+----------------------------+---------------------------------------------+--------
 tablefunc | connectby   | SETOF record               | text, text, text, text, integer             | normal
 tablefunc | connectby   | SETOF record               | text, text, text, text, integer, text       | normal
 tablefunc | connectby   | SETOF record               | text, text, text, text, text, integer       | normal
 tablefunc | connectby   | SETOF record               | text, text, text, text, text, integer, text | normal
 tablefunc | crosstab    | SETOF record               | text                                        | normal
 tablefunc | crosstab    | SETOF record               | text, integer                               | normal
 tablefunc | crosstab    | SETOF record               | text, text                                  | normal
 tablefunc | crosstab2   | SETOF tablefunc_crosstab_2 | text                                        | normal
 tablefunc | crosstab3   | SETOF tablefunc_crosstab_3 | text                                        | normal
 tablefunc | crosstab4   | SETOF tablefunc_crosstab_4 | text                                        | normal
 tablefunc | normal_rand | SETOF double precision     | integer, double precision, double precision | normal
(11 rows)

testdb=# 

★ use test

★ swipe in the test table and data

--Test table
create table score(
name varchar,
subject varchar,
score bigint
);
--test data
insert into  score values
('Lucy','English',100),
('Lucy','Physics',90),
('Lucy','Math',85),
('Lily','English',76),
('Lily','Physics',57),
('Lily','Math',86),
('David','English',57),
('David','Physics',86),
('David','Math',100),
('Simon','English',88),
('Simon','Physics',99),
('Simon','Math',65);
--Original data query
select * from score order by 1,2,3;

★ sql standard implementation

select name,
	sum(case when subject='English' then score else 0 end) as "English",
	sum(case when subject='Physics' then score else 0 end) as "Physics",
	sum(case when subject='Math' then score else 0 end) as "Math"
	from score
	group by name order by name desc;

 name  | English | Physics | Math 
-------+---------+---------+------
 Simon |      88 |      99 |   65
 Lucy  |     100 |      90 |   85
 Lily  |      76 |      57 |   86
 David |      57 |      86 |  100
(4 rows)

★ tablefunc implementation

select * from 
	crosstab('select name,subject,score from score order by name desc',   
    /* name:Group standard, subject: aggregation standard, score: calculated value under aggregation standard */
	$$values('English'::text),('Physics'::text),('Math'::text)$$           
	)
	as score(name text,English bigint,Physics bigint,Math bigint);        
    /*Display field name,English,Physics,Math   
      [name Is the grouping standard; English,Physics,Math is the field name generated by the aggregation standard]
    */


 name  | english | physics | math 
-------+---------+---------+------
 Simon |      88 |      99 |   65
 Lucy  |     100 |      90 |   85
 Lily  |      76 |      57 |   86
 David |      57 |      86 |  100
(4 rows)

Reference resources:

       http://www.bubuko.com/infodetail-2159755.html