Re: Crosstab function

Very Clear instruction !

Thank you very much David. I will do it in my client app and follow your
guidance.

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of David G Johnston
Sent: Tuesday, May 06, 2014 11:01 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Crosstab function

Hengky Lie wrote
> Hi David,
>
> Are you sure that there is no pure sql solution for this ?
>
> I think (with my very limited postgres knowledge), function can solve
> this.
>
> Which is the column header I need but I really have no idea how to use
> this
> as column header.
>
> Anyway, If i can't do this in postgres, I will try to build sql string in
> the client application (Windev) and send the fixed sql to the server

Positive.

You could build the necessary string in a pl/pgsql language function but you
would still have trouble getting the data out of the function the way you
want; unless you output a single text column no matter the original data (
basically output a cvs version of the crosstab result).

There is no dynamic execution in SQL so even though you can get a string
that looks like what you want you cannot do anything with it. Only data is
allowed to be dynamic; the engine has to know the names and types of all
schema objects before it can start so there is no way a query can retrieve
these things from its own data. It's the whole cart-horse thing...

The solution is to build the query in the client and send it. Make sure you
look at the various "quote_" functions in order to minimize the risk of SQL
injection attacks. These are especially useful for pl/pgsql functions but
you might be able to use them in your first query so that you can avoid
coding all the quoting and escaping rules into your application. At minimum
double-quote all your identifiers and make sure there are no unescaped
embedded double-quotes. If the only variables are from data in tables
putting constraints on those tables would probably be useful as well - you
limit valid identifiers but minimized risk of bad data causing an issue.

David J.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601.
html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general