Add fields to view from select (transpose fields) I've got a table with following structure:

CREATE TABLE test
(
  id integer,
  field character varying, 
  value character varying
);

field     value
------    -----
field1    value1
field2    value2
field3    value3
...       ...


I need to transpose the field value as the fields in a view and the value of the field with be the "value" field's value. Example of what I need:

field1    field2    field3      ...
------    -----     ------      ----
value1    value2    value3      ...


I have no idea on how to start building the create view statement.
For the simple case you present, build an array from your sorted values and unnest one by one in the SELECT list

SELECT arr[1] AS field1
     , arr[2] AS field2
     , arr[3] AS field3
FROM  (SELECT ARRAY(SELECT value FROM test ORDER BY field) AS arr) sub;


Obviously, this is not dynamic, but only works for given fields. You did not actually ask for "dynamic" - except that you added the tag dynamic-sql.

For more complex operations use crosstab() from the additional module tablefunc. Detailed instructions:


PostgreSQL Crosstab Query


Still not "dynamic". A completely dynamic function is hardly possible since SQL demands to know the return type at the time of the function call. If the return type is dynamic, you need two steps:


build your query dynamically
execute it.


But there may be something for you here:


Dynamic alternative to pivot with CASE and GROUP BY