由网友(平凡之路i)分享简介：I am having difficulties writing a Postgres function, as I am not familiar with it. I have multiple tables to import into Postgres with this format:id | 1960 |...

I am having difficulties writing a Postgres function, as I am not familiar with it. I have multiple tables to import into Postgres with this format:

id | 1960 | 1961 | 1962 | 1963 | ...
____________________________________
 1    23     45     87     99
 2    12     31    ...

which I need to convert into this format:

id | year | value
_________________
 1   1960    23
 1   1961    45
 1   1962    87
 ...
 2   1960    12
 2   1961    31
 ...

I would imagine the function too to read like this:

SELECT all-years FROM imported_table;
CREATE a new_table;
FROM min-year TO max-year LOOP
     EXECUTE "INSERT INTO new_table (id, year, value) VALUES (id, year, value)";
END LOOP;

However, I'm having real trouble writing the nitty-gritty details for this. Would be easier for me to do that in PHP, but I am convinced that it's cleaner to do it directly in a Postgres-function.

The years (start and end) vary from table to table. And sometimes, I can even have years only for every fifth year or so ...

解决方案

A completely dynamic version requires dynamic SQL. Use a plpgsql function with EXECUTE:

For Postgres 9.2 or older (before LATERAL was implemented):

CREATE OR REPLACE FUNCTION f_unpivot_years92(_tbl regclass, VARIADIC _years int[])
  RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE '
   SELECT id
        , unnest($1) AS year
        , unnest(ARRAY["'|| array_to_string(_years, '","') || '"]) AS val
   FROM   ' || _tbl || '
   ORDER  BY 1, 2'
   USING _years;
END
$func$  LANGUAGE plpgsql;

For Postgres 9.3 or later (with LATERAL):

CREATE OR REPLACE FUNCTION f_unpivot_years(_tbl regclass, VARIADIC _years int[])
  RETURNS TABLE(id int, year int, value int) AS
$func$
BEGIN
   RETURN QUERY EXECUTE (SELECT
     'SELECT t.id, u.year, u.val
      FROM  ' || _tbl || ' t
      LEFT   JOIN LATERAL (
         VALUES ' || string_agg(format('(%s, t.%I)', y, y), ', ')
     || ') u(year, val) ON true
      ORDER  BY 1, 2'
      FROM   unnest(_years) y
      );
END
$func$  LANGUAGE plpgsql;

About VARIADIC:

Return rows matching elements of input array in plpgsql function

Call for arbitrary years:

SELECT * FROM f_unpivot_years('tbl', 1961, 1964, 1963);

Same, passing an actual array:

SELECT * FROM f_unpivot_years('tbl', VARIADIC '{1960,1961,1962,1963}'::int[]);

For a long list of sequential years:

SELECT * 
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2014)));

For a long list with regular intervals (example for every 5 years):

SELECT *
FROM f_unpivot_years('t', VARIADIC ARRAY(SELECT generate_series(1950,2010,5)));

Output as requested.

The function takes: 1. A valid table name - double-quoted if it's otherwise illegal (like '"CaMeL"'). Using the object identifier type regclass to assert correctness and defend against SQL injection. You may want to schema-qualify the tale name to be unambiguous (like 'public."CaMeL"'). More:

Table name as a PostgreSQL function parameter

2. Any list of numbers corresponding to (double-quoted) column names. Or an actual array, prefixed with the keyword VARIADIC.

The array of columns does not have to be sorted in any way, but table and columns must exist or an exception is raised.

Output is sorted by id and year (as integer). If you want years to be sorted according to the sort order of the input array, make it just ORDER BY 1. Sort order according to array is not strictly guaranteed, but works in the current implementation. More about that:

PostgreSQL unnest() with element number

Also works for NULL values.

SQL Fiddle for both with examples.

References:

Is there something like a zip() function in PostgreSQL that combines two arrays?

Table name as a PostgreSQL function parameter

PostgreSQL generate_series() with SQL function as arguments