Crosstab function

Hengky Lie wrote

The crosstab warehouse column name (OFFICE & Store2) is taken from
tblwarehouse so when user add warehouse, crosstab column name will change
automatically. And also each row has total qty.

In what programming language? The only way to do this is to dynamically
construct the appropriate query, with the correct number of columns,
on-the-fly in the client application and send it as a normal query to the
server. There is no pure SQL solution.

For the total column you will need a virtual warehouse that holds those
values. Likely the easiest way to get that will be to UNION ALL the main
real warehouse query and another query the groups by product and sum-counts
that values from the individual warehouses.

IIRC you've already been shown how to write the basic crosstab query; this
really isn't any different but you will need procedural logic and some way
to dynamically build a SQL query string based upon how many warehouses you
have at the time you run the query.

I am assuming you know how to write the basic join query to get the general
form needed for the real warehouse data.

David J.

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

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

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.

So far i can use command:

select *
from crosstab
(
'select produkid, warehouseid, onhand
from tblproduct order by 1',
'select distinct warehouseid from tblproduct order by 1'
)
as ct (produkid text, office int, store2 int);

and I have this result :

PRODUKID | OFFICE | STORE2 |
---------------+--------+--------+
2791404000014 | 10 | 45 |
2791404000021 | | 10 |

The problem is the column header is static. If I have new warehouse, I
should manually add it in the column header.

IF I use command : select 'Produk ID text, ' || array_to_string(array(select
warehousename from tblwarehouse), ' int, ') || ' int';

I can get : "Produk ID text, OFFICE int, STORE2 int"

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

Thanks

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G Johnston
Sent: Monday, May 05, 2014 10:25 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Crosstab function

Hengky Lie wrote

The crosstab warehouse column name (OFFICE & Store2) is taken from
tblwarehouse so when user add warehouse, crosstab column name will
change automatically. And also each row has total qty.

In what programming language? The only way to do this is to dynamically
construct the appropriate query, with the correct number of columns,
on-the-fly in the client application and send it as a normal query to the
server. There is no pure SQL solution.

For the total column you will need a virtual warehouse that holds those
values. Likely the easiest way to get that will be to UNION ALL the main
real warehouse query and another query the groups by product and sum-counts
that values from the individual warehouses.

IIRC you've already been shown how to write the basic crosstab query; this
really isn't any different but you will need procedural logic and some way
to dynamically build a SQL query string based upon how many warehouses you
have at the time you run the query.

I am assuming you know how to write the basic join query to get the general
form needed for the real warehouse data.

David J.

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

Are you sure that there is no pure sql solution for this ?

There is no pure SQL solution because a SQL query always gives a fixed
number of columns. You could compose the SQL in your client app and
vary the columns by the current warehouses. Or you could say GROUP BY
produkit, tblwarehouse.id and rearrange the result client-side. I'd
say those are the practical approaches, but if they aren't possible,
you may also be able to use Postgres's array feature, so that your
result columns are:

produkid
warehouse_totals[]
total

Someone asked a similar question about a year ago, and I wrote up how
to solve it with arrays and a recursive CTE here:

http://www.illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/

Good luck,
Paul

On Mon, May 5, 2014 at 7:37 PM, Hengky Liwandouw
<hengkyliwandouw@gmail.com> 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.

So far i can use command:

select *
from crosstab
(
'select produkid, warehouseid, onhand
from tblproduct order by 1',
'select distinct warehouseid from tblproduct order by 1'
)
as ct (produkid text, office int, store2 int);

and I have this result :

PRODUKID | OFFICE | STORE2 |
---------------+--------+--------+
2791404000014 | 10 | 45 |
2791404000021 | | 10 |

The problem is the column header is static. If I have new warehouse, I
should manually add it in the column header.

IF I use command : select 'Produk ID text, ' || array_to_string(array(select
warehousename from tblwarehouse), ' int, ') || ' int';

I can get : "Produk ID text, OFFICE int, STORE2 int"

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

Thanks

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G Johnston
Sent: Monday, May 05, 2014 10:25 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Crosstab function

Hengky Lie wrote

The crosstab warehouse column name (OFFICE & Store2) is taken from
tblwarehouse so when user add warehouse, crosstab column name will
change automatically. And also each row has total qty.

In what programming language? The only way to do this is to dynamically
construct the appropriate query, with the correct number of columns,
on-the-fly in the client application and send it as a normal query to the
server. There is no pure SQL solution.

For the total column you will need a virtual warehouse that holds those
values. Likely the easiest way to get that will be to UNION ALL the main
real warehouse query and another query the groups by product and sum-counts
that values from the individual warehouses.

IIRC you've already been shown how to write the basic crosstab query; this
really isn't any different but you will need procedural logic and some way
to dynamically build a SQL query string based upon how many warehouses you
have at the time you run the query.

I am assuming you know how to write the basic join query to get the general
form needed for the real warehouse data.

David J.

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

--
_________________________________
Pulchritudo splendor veritatis.

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

Why not use the crosstab stuff in contrib?
http://www.postgresql.org/docs/9.3/static/tablefunc.html

Has it been removed or something?

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Paul Jungwirth
Sent: Monday, May 5, 2014 7:49 PM
To: Hengky Liwandouw
Cc: pgsql-general General
Subject: Re: [GENERAL] Crosstab function

Are you sure that there is no pure sql solution for this ?

There is no pure SQL solution because a SQL query always gives a fixed number of columns. You could compose the SQL in your client app and vary the columns by the current warehouses. Or you could say GROUP BY produkit, tblwarehouse.id and rearrange the result client-side. I'd say those are the practical approaches, but if they aren't possible, you may also be able to use Postgres's array feature, so that your result columns are:

produkid
warehouse_totals[]
total

Someone asked a similar question about a year ago, and I wrote up how to solve it with arrays and a recursive CTE here:

http://www.illuminatedcomputing.com/posts/2013/03/fun-postgres-puzzle/

Good luck,
Paul

On Mon, May 5, 2014 at 7:37 PM, Hengky Liwandouw <hengkyliwandouw@gmail.com> 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.

So far i can use command:

select *
from crosstab
(
'select produkid, warehouseid, onhand
from tblproduct order by 1',
'select distinct warehouseid from tblproduct order by 1'
)
as ct (produkid text, office int, store2 int);

and I have this result :

PRODUKID | OFFICE | STORE2 |
---------------+--------+--------+
2791404000014 | 10 | 45 |
2791404000021 | | 10 |

The problem is the column header is static. If I have new warehouse, I
should manually add it in the column header.

IF I use command : select 'Produk ID text, ' ||
array_to_string(array(select warehousename from tblwarehouse), ' int,
') || ' int';

I can get : "Produk ID text, OFFICE int, STORE2 int"

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

Thanks

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G
Johnston
Sent: Monday, May 05, 2014 10:25 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Crosstab function

Hengky Lie wrote

The crosstab warehouse column name (OFFICE & Store2) is taken from
tblwarehouse so when user add warehouse, crosstab column name will
change automatically. And also each row has total qty.

In what programming language? The only way to do this is to
dynamically construct the appropriate query, with the correct number
of columns, on-the-fly in the client application and send it as a
normal query to the server. There is no pure SQL solution.

For the total column you will need a virtual warehouse that holds
those values. Likely the easiest way to get that will be to UNION ALL
the main real warehouse query and another query the groups by product
and sum-counts that values from the individual warehouses.

IIRC you've already been shown how to write the basic crosstab query;
this really isn't any different but you will need procedural logic and
some way to dynamically build a SQL query string based upon how many
warehouses you have at the time you run the query.

I am assuming you know how to write the basic join query to get the
general form needed for the real warehouse data.

David J.

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

--
_________________________________
Pulchritudo splendor veritatis.

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

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

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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of David G Johnston
Sent: Tuesday, May 06, 2014 11:01 AM
To: pgsql-general@postgresql.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

<html style="direction: ltr;">
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
<style type="text/css">body p { margin-bottom: 0cm; margin-top: 0pt; } </style>
</head>
<body style="direction: ltr;"
bidimailui-detected-decoding-type="latin-charset" bgcolor="#FFFFFF"
text="#000000">
<div class="moz-cite-prefix">What I have done in the past to build a
generic reporting application is to have the function write the
results you want in a table and return the tablename and then have
the client code call select * from that table. <br>
<br>
My standard report tablename is tblreport || userid;<br>
It gets dropped at the beginning of the function, so it is
basically a temp table that doesn't interfere with any other
users.<br>
<br>
Example:<br>
&nbsp;&nbsp;&nbsp; execute 'drop table if exists reports.tblreport' || v_userid ;<br>
&nbsp;&nbsp;&nbsp; execute 'drop sequence if exists reports.tblreport' ||
v_userid || '_id_seq; create sequence reports.tblreport' ||
v_userid || '_id_seq';<br>
&nbsp;&nbsp;&nbsp; v_sql=' create table reports.tblreport' || v_userid || ' as ';<br>
<br>
Sim<br>
<br>
On 05/06/2014 06:37 AM, Hengky Liwandouw wrote:<br>
</div>
<blockquote cite="mid:004601cf68dc$848e8420$8dab8c60$@com"
type="cite">
<pre wrap="">Very Clear instruction !

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

-----Original Message-----
From: <a class="moz-txt-link-abbreviated" href="mailto:pgsql-general-owner@postgresql.org">pgsql-general-owner@postgresql.org</a>
[<a class="moz-txt-link-freetext" href="mailto:pgsql-general-owner@postgresql.org">mailto:pgsql-general-owner@postgresql.org</a>] On Behalf Of David G Johnston
Sent: Tuesday, May 06, 2014 11:01 AM
To: <a class="moz-txt-link-abbreviated" href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>
Subject: Re: [GENERAL] Crosstab function

Hengky Lie wrote
</pre>
<blockquote type="cite">
<pre wrap="">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
</pre>
</blockquote>
<pre wrap="">
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:
<a class="moz-txt-link-freetext" href="http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601&quot;&gt;http://postgresql.1045698.n5.nabble.com/Crosstab-function-tp5802402p5802601&lt;/a&gt;.
html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

</pre>
</blockquote>
<br>
</body>
</html>

Thanks Sim, a very usefull information.

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sim Zacks
Sent: Wednesday, May 07, 2014 7:33 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Crosstab function

What I have done in the past to build a generic reporting application is to
have the function write the results you want in a table and return the
tablename and then have the client code call select * from that table.

My standard report tablename is tblreport || userid;
It gets dropped at the beginning of the function, so it is basically a temp
table that doesn't interfere with any other users.

Example:
execute 'drop table if exists reports.tblreport' || v_userid ;
execute 'drop sequence if exists reports.tblreport' || v_userid ||
'_id_seq; create sequence reports.tblreport' || v_userid || '_id_seq';
v_sql=' create table reports.tblreport' || v_userid || ' as ';

Sim