从一个数据类型/record/row中解析它对应的列名

背景

从一个数据类型/record/row中解析它对应的列名，这个在触发器中封装SQL是非常有用的。

现在有这样的插件来帮助你完成这样的事情了。

http://api.pgxn.org/src/colnames/colnames-1.5.0/doc/colnames.mmd

colnames 1.1.0  
==============  
  
Synopsis  
--------  
  
    CREATE EXTENSION colnames;  
  
    SELECT colnames( ROW(1, 'foo', 458.0) );  
      colnames    
    ------------  
     {f1,f2,f3}  
  
    SELECT colnames( ROW(c.*)::pg_collation ) FROM pg_collation c LIMIT 1;  
                                   colnames                                  
    -----------------------------------------------------------------------  
     {collname,collnamespace,collowner,collencoding,collcollate,collctype}  
  
Description  
-----------  
  
This extension contains a single SQL function, `colnames()`, that takes a  
record value as its argument and returns an array of the names of the columns  
in that record. This can be useful for example in trigger functions, where one  
might need to get the column names in order to generate a query string.  
  
Usage  
-----  
  
Simply pass a record value to the function and it will return an array of  
the column names for that record:  
  
    SELECT colnames( ROW(1, 'foo', 458.0) );  
      colnames    
    ------------  
     {f1,f2,f3}  
  
Note that this function is not strict; it's legal to pass in a null record of  
a named composite type:  
  
    SELECT colnames( NULL::pg_collation );  
                                   colnames                                  
    -----------------------------------------------------------------------  
     {collname,collnamespace,collowner,collencoding,collcollate,collctype}  
  
**Warning:** The indexes of the resulting array do **not** necessarily  
correspond to the values one might find when fetching column names from  
`pg_attribute.attnum`, since columns are not returned. The resulting array  
**does** correspond to the order of fields that would be returned by, say,  
`SELECT (rec).*`.  
  
Support  
-------  
  
This library is stored in an open [GitHub  
repository](http://github.com/theory/colnames). Feel free to fork and  
contribute! Please file bug reports via [GitHub  
Issues](http://github.com/theory/colnames/issues/).  
  
Author  
------  
  
[Andrew Gierth](http://blog.rhodiumtoad.org.uk/)  
[David E. Wheeler](http://www.justatheory.com/)  
  
Copyright and License  
---------------------  
  
Copyright (c) 2011 Andrew Gierth and David E. Wheeler.  
  
This module is free software; you can redistribute it and/or modify it under  
the [PostgreSQL License](http://www.opensource.org/licenses/postgresql).  
  
Permission to use, copy, modify, and distribute this software and its  
documentation for any purpose, without fee, and without a written agreement is  
hereby granted, provided that the above copyright notice and this paragraph  
and the following two paragraphs appear in all copies.  
  
In no event shall Andrew Gierth or David E. Wheeler be liable to any party for  
direct, indirect, special, incidental, or consequential damages, including  
lost profits, arising out of the use of this software and its documentation,  
even if Andrew Gierth or David E. Wheeler has been advised of the possibility  
of such damage.  
  
Andrew Gierth and David E. Wheeler specifically disclaim any warranties,  
including, but not limited to, the implied warranties of merchantability and  
fitness for a particular purpose. The software provided hereunder is on an "as  
is" basis, and Andrew Gierth and David E. Wheeler have no obligations to  
provide maintenance, support, updates, enhancements, or modifications.  

digoal’s 大量PostgreSQL文章入口