title: How to creating dynamic columns using data PostgreSQL 9.1?
body: <p>So first off, I am completely new to databases and learning as I go. I have a table that comes from puppetdb in which I use postgresql as the backend.  There are approximately 65 to 70 values for each certname (which can grow with custom facts) and my table looks like this: </p>
  
  <p>TableName: certname_facts</p>
  
  <pre><code>  certname       name          value
   ------------------------------------
    node1          kernel        Linux
    node1          memorysize    1.96 GB
    node1          osfamily      Debian
    node1          netmask eth0  255.255.255.0
        .             .             . 
        .             .             .
        .             .             .
    node2          kernel        Windows
    node2          memorysize    2.16 GB
    node2          osfamily      Windows
    node2          netmask eth0  255.255.255.0
        .             .             . 
        .             .             .
        .             .             .
  </code></pre>
  
  <p>I was able to create a view using this link: <a href="http://www.cureffi.org/2013/03/19/automatically-creating-pivot-table-column-names-in-postgresql/" rel="nofollow">Automatically creating pivot table column names in PostgreSQL</a></p>
  
  <pre><code>create or replace function pivotcode (tablename varchar, rowc varchar, colc varchar,     cellc varchar, celldatatype varchar) returns varchar language plpgsql as $$
  declare
     dynsql1 varchar;
     dynsql2 varchar;
     columnlist varchar;
  begin
  
  dynsql1 = 'select string_agg(distinct ''_''||'||colc||'||'' '||celldatatype||''','','' order by ''_''||'||colc||'||'' '||celldatatype||''') from '||tablename||';';
  execute dynsql1 into columnlist;
  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 newtable ('||rowc||' varchar,'||columnlist||');';
  return dynsql2;
  end
  $$
  
  
  --output of table
  certname   kernel      memorysize   osfamily   netmask eth0   . . .
  ------------------------------------------------------------------------
  node1      Linux       1.96 GB      Debian     255.255.255.0   . . .
  node2      Windows     2.16 GB      Windows    255.255.255.0   . . . 
  </code></pre>
  
  <p>The problem I am running into is that sometimes certain nodes don't have the same parameters and parameters can grow such that. </p>
  
  <pre><code>  certname       name          value
    -----------------------------------------
    node1          kernel        Linux
    node1          memorysize    1.96 GB
    node1          osfamily      Debian
    node1          netmask eth0  255.255.255.0
    node1          location       home
        .             .             .
    node2          kernel        Windows
    node2          memorysize    2.16 GB
    node2          osfamily      Windows
    node2          netmask eth0  255.255.255.0
  
    node3         kernel          linux
    node3         osfamily        Ubuntu
    node3         netmask eth0  255.255.255.0
  </code></pre>
  
  <p>So when i go to access the view it fails.   Any suggestion on how to handle this type of situation. Is there a way to make a view completely dynamic and change based on the data.  I currently looking into triggers and dynamic sql as an option. </p>
  
  <hr>
  
  <p>Here is the output of the function created with it updated with a new dynsql2 statement. </p>
  
  <p><code>select * from crosstab ('select a.certname, a.colname, b.value from ( select          certname,colname from (select distinct certname) left join columns on 1=1) a left join(select certname as certname,nameas col name ,value value from certname_facts group by 1,2 ) b on a.certname = b.certname and a.colname = b.colname) a','select distinct name from certname_facts order by 1') as newtable (certname varchar,_architecture   varchar,_augeasversion varchar,_clientcert varchar,_clientversion varchar,_domain varchar,_environment varchar,_facterversion varchar,_fqdn varchar,_hardwareisa varchar,_hardwaremodel varchar,_hostname varchar,_id varchar,_interfaces varchar,_ipaddress_eth0 varchar,_ipaddress_lo varchar,_ipaddress varchar,_is_virtual varchar,_kernelmajversion varchar,_kernelrelease varchar,_kernel varchar,_kernelversion varchar,_lsbdistcodename varchar,_lsbdistdescription varchar,_lsbdistid varchar,_lsbdistrelease varchar,_lsbmajdistrelease varchar,_macaddress_eth0 varchar,_macaddress varchar,_manufacturer varchar,_memoryfree varchar,_memorysize varchar,_memorytotal varchar,_netmask_eth0 varchar,_netmask_lo varchar,_netmask varchar,_network_eth0 varchar,_network_lo varchar,_operatingsystemrelease varchar,_operatingsystem varchar,_osfamily varchar,_path varchar,_physicalprocessorcount varchar,_processor0 varchar,_processorcount varchar,_productname varchar,_ps varchar,_puppetversion varchar,_rubysitedir varchar,_rubyversion varchar,_selinux varchar,_serialnumber varchar,_sshdsakey varchar,_sshecdsakey varchar,_sshrsakey varchar,_swapfree varchar,_swapsize varchar,__timestamp varchar,_timezone varchar,_type varchar,_uniqueid varchar,_uptime_days varchar,_uptime_hours varchar,_uptime_seconds varchar,_uptime varchar,_virtual varchar);</code>  </p>