Greenplum obtains the field name of an SQL result.

In greenplum, it is difficult to obtain the field names after execution of any SQL statement.

For example, when writing a common tool, you can use the copy command to export an SQL statement to text, but the text exported by the name of each field does not exist, if you use your own SQL parsing, it will be too complicated.

If we want to obtain these field names, we do not actually execute the SQL statement, because the execution plan has been generated, and I know the following methods:

1. Use JDBC and preparestatement to generate an execution plan and obtain the field name:

             PreparedStatement pstmt = conn.prepareStatement("select * from gp_segment_configuration a");             ResultSetMetaData metaData=pstmt.getMetaData();             for(int i=1;i<=metaData.getColumnCount();i++){              System.out.println(metaData.getColumnName(i)+" \t\t :"+metaData.getColumnTypeName(i));             }

PS: it is not just JDBC, but other client interfaces are estimated to have such interfaces to retrieve fields. After reading the JDBC source code, when obtaining the metadata information, it directly interacts with the database and receives the information. For others, there should be a defined interface, which needs to be mined by yourself.

2. If verbose is added to the explain statement, the number of parsed syntaxes is printed. As follows:

aligputf8=# explain verbose select * from cxfa;                              QUERY PLAN                           ----------------------------------------------------------------    {MOTION     :motionID 1     :motionType 1     :sendToSecondary false     :sendSorted false     :numInputSegs 3     :inputSegIdx <>     :numHashSegs 0     :hashSegIdx     :hashExpr <>     :hashDataTypes <>     :numOutputSegs 1     :outputSegIdx -1     :numSortCols 0     :sortColIdx     :sortOperators     :plan_node_id 1     :plan_parent_node_id -1     :startup_cost 0.00     :total_cost 0.00     :plan_rows 1     :plan_width 36 

The top targetlist is the list of fields to be returned by SQL.

   :targetlist (       {TARGETENTRY        :expr           {VAR           :varno 1           :varattno 1           :vartype 23           :vartypmod -1           :varlevelsup 0           :varnoold 1           :varoattno 1          }       :resno 1        :resname a        :ressortgroupref 0        :resorigtbl 28569230        :resorigcol 1        :resjunk false       }       {TARGETENTRY        :expr           {VAR           :varno 1           :varattno 2           :vartype 25           :vartypmod -1           :varlevelsup 0           :varnoold 1           :varoattno 2          }       :resno 2        :resname b        :ressortgroupref 0        :resorigtbl 28569230        :resorigcol 2        :resjunk false       }    )

In this way, can we use this to write a function to obtain the field name? It is still difficult to parse the syntax tree directly. We found that each field name must start with resname. We can traverse this number and find all the fields starting with ": resname", that is, the field name.

The function code is as follows:

CREATE or replace FUNCTION public.get_sql_column_name(sqlori text)      RETURNS setof textAS $    sql_ori=sqlori.strip().lower()    if not sql_ori.startswith('select'):        return ['SQL is not Select SQL,Please check!']    retext=[]    sql="explain verbose " + sql_ori    #plpy.info(sql)    rv = plpy.execute(sql)    for i in rv:        if i['QUERY PLAN'].startswith("      :resname"):            retext.append(i['QUERY PLAN'].replace("      :resname",''))    return retext;$ LANGUAGE plpythonu;

The execution result is as follows:

aligputf8=# select get_sql_column_name('select * from cxfa'); get_sql_column_name ---------------------  a   b (2 rows)Time: 32.037 msaligputf8=# select get_sql_column_name(pg_get_viewdef('pg_partitions'));    get_sql_column_name     ----------------------------  schemaname   tablename   partitionschemaname   partitiontablename   partitionname   parentpartitiontablename   parentpartitionname   partitiontype   partitionlevel   partitionrank   partitionposition   partitionlistvalues   partitionrangestart   partitionstartinclusive   partitionrangeend   partitionendinclusive   partitioneveryclause   partitionisdefault   partitionboundary (19 rows)Time: 44.404 ms