How to include a function in the AS part of query?

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.

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

There are tricky ways around this .. You could create a plpgsql 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 plpgsql for this.

Consider my comprehensive answer under this related question:
Refactor a PL/pgSQL function to return the output of various SELECT queries
The second half of the answer is for you.

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