How to creating dynamic columns using data PostgreSQL 9.1?

Question

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:

TableName: certname_facts

  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
      .             .             . 
      .             .             .
      .             .             .

I was able to create a view using this link: Automatically creating pivot table column names in PostgreSQL

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   . . .

The problem I am running into is that sometimes certain nodes don't have the same parameters and parameters can grow such that.

  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

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.

Here is the output of the function created with it updated with a new dynsql2 statement.

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);

It'd be helpful if you posted your code that created this...but what you want to do is create a list of columns you expect and then left join to your data...this produces nulls when the column doesn't exist for that certname. Same idea as here I think: http://stackoverflow.com/questions/8490478/transposing-an-sql-result-so-that-one-column-goes-onto-multiple-columns/8494779#8494779 — Twelfth, Jul 02 '14 at 20:58
`It fails`? Please add the verbatim error message. Also, the actual view definition is missing, as well as your version of Postgres. This related answer may be of help: http://stackoverflow.com/questions/15506199/dynamic-alternative-to-pivot-with-case-and-group-by/15514334#15514334 — Erwin Brandstetter, Jul 03 '14 at 16:33
RROR: subquery in FROM must have an alias LINE 1: ...name, b.value from ( select certname,colname from (select di... ^ HINT: For example, FROM (SELECT ...) [AS] foo. QUERY: 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 ********** Error ********** — user990951, Jul 03 '14 at 17:08
This function definition does not seem to match the error message's type and query. Also you have not shown the function call that caused it. Please give a function definition, function call and error message including query that all match. — philipxy, Jul 14 '14 at 04:16

score 0 · Answer 1 · answered Jul 02 '14 at 21:31

I get the feeling this will be a work in progress type answer that requires quite a few updates to get it right. There is likely a better way of doing this too, but this should work. You'll need to take the idea here and taylor it for your example...

Going to focus on the cross tab query line that is setting up the initial data (not the column list):

'select * from crosstab (''select '||rowc||','||colc||','||cellc||' from '||tablename||' group by 1,2 order by 1,2''...

We want to alter this statement so if a column is not found, a null is returned for that column instead.

some degree of psuedo code here:

select * from (select distinct certname) left join columns on 1=1

(columns being the columnlist you build earlier)

This cross join query gives a list of cert name and the column you wished to be populated. now join the list here to the values in your dynqry2 statement.

(my apologies in advance, this is a weird code merge of the dynamic stuff provided above and my own psuedo code. You will have to find a way to set dynsql2 to this statement):

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 '||rowc||' as certname,'||colc||'as col name ,'||cellc||' value 
     from '||tablename||' group by 1,2 ) b
on a.certname = b.certname and a.colname = b.colname) a

Plug this into the first crosstab parameter in the dynqry2 statement and give a try...

score 0 · Answer 2 · answered Jul 14 '14 at 06:17

Two-parameter crosstab uses NULL for cellc/value where a given rowc/certname has no colc/name row in tablename/certname_facts. (Ie it does a LEFT JOIN, as your query ouptput shows.) See the F.36.1.4. crosstab(text, text) examples.

dynsql2 = '
    select * from crosstab (
        ''select '||rowc||','||colc||','||cellc||'
          from '||tablename||'
          order by 1'',
        ''select distinct '||colc||'
          from '||tablename||'
          order by 1''
    )
    as ('||rowc||' varchar,'||columnlist||')
    ';

Order in columnlist must be the same as in the select distinct.

How to creating dynamic columns using data PostgreSQL 9.1?

2 Answers2