How to use dynamic names in a column definition list?

Question

I query the Postgres database to get statistics for the 4 last years.
However, I cannot figure out the correct syntax to replace the hard-coded date in the AS part of the query with a function (the column definition list).

Say: date_part('year',current_date)-1 instead of "2012"

SELECT * FROM crosstab('
SELECT client,date_part (''year'',date) as SalesDate
       ,Sum(total)::integer AS Synthese
FROM statistic
WHERE date_part(''year'',date)>date_part(''year'',current_date)-5
AND date_part(''year'',date)<date_part(''year'',current_date)
GROUP BY client,SalesDate
ORDER BY 1,2',
$$VALUES 
(date_part('year',current_date)-4),
(date_part('year',current_date)-3),
(date_part('year',current_date)-2),
(date_part('year',current_date)-1)$$)
AS ( client text, "2009" text, "2010" text , "2011" text, "2012" text);

Answer 1

Answer

A principle of SQL: You can provide values dynamically, but identifiers are static.

The outer call is a basic SQL statement, the column definition list consists of identifiers, not values. (That's why you are forced to double-quote your otherwise illegal identifiers starting with a digit.) Therefore, you cannot use a function call in this place. Just not possible.

Possible workaround

You could create a PL/pgSQL function that returns a polymorphic composite type and hand in a parameter of a well defined composite type, which also carries column names. But that's highly advanced stuff. You need to be up to speed with PL/pgSQL for this. See:

• Refactor a PL/pgSQL function to return the output of various SELECT queries

Since you need a well-defined composite type you'd have to create a type before calling the function this way. Or just create a temporary table (that provides a type automatically). You could automate this with a DO statement that uses dynamic SQL to derive column names from the current date ...
I never said it was simple, but it's possible.

Base query

In the meanwhile, your crosstab() query could be improved:

SELECT *
FROM   crosstab(
    $$
    SELECT client
         , date_part ('year', date) AS salesdate
         , sum(total)::int AS synthese
    FROM   statistic
    WHERE  date >= date_trunc('year', now()) - interval '5y'
    AND    date <  date_trunc('year', now())
    GROUP  BY 1,2
    ORDER  BY 1,2
    $$

    ,$$VALUES 
        (date_part('year', now()) - 4)
      , (date_part('year', now()) - 3)
      , (date_part('year', now()) - 2)
      , (date_part('year', now()) - 1)
    $$
    ) AS c(client text, "2009" text, "2010" text, "2011" text, "2012" text);

• Use dollar-quoting to avoid quoting hell.
• Rewrite your WHERE conditions so they can use indexes.
  Detailed explanation in this closely related answer.

The rest is a matter of taste and style.