Some SQL tips used for a puppet migration project

Puppet console stores its info in a DB, usually postgres, we needed to extract some info from that for a puppet migration project.
the tables we were looking at where nodes table and parameters table.
Those needed to be joined so we can extract all the parameters that are defined for a node as follows:


select nodes.name, parameters.key, parameters.value from nodes,parameters where

nodes.id = parameters.parameterable_id

order by nodes.name

The output of this query was exported and cleaned up with "sed" so that we can import it as a table once more.

however, puppet stores the data as key value items tied to each node name, thus if you have 3 paramters per node, you end up with 3 records with same node name and different key value pairs.

To transform this to a relational DB like table, I used this query with help of my colleague Mohamed Youssef, our Senor DB engineer:

create view nodes as (

select r.name , a.value as val1 ,r.value as val2, g.value as val3

from

(select name, value from param where key='val1') r , (select name, value from param where key='val2') a , (select name, value from param where key='val3') g

where r.name=g.name

and r.name=a.name )