Re: Return and sql tuple descriptions are incompatible - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

(resending to the list as well for the sake of the archives)

On 04/25/2014 08:53 AM, Hengky Liwandouw wrote:
> Thanks to give me the right direction to get help. Okay, here it
> the detail.
>
> CREATE table test (id SERIAL, produkid TEXT, warehousename TEXT,
> onhand INTEGER);
>
> COPY test (id, produkid, warehousename, onhand) FROM stdin; 1
> 2791404000014    OFFICE    10 2    2791404000021    STORE2    10 3    2791404000014
> STORE2    45 \.
>
> select * from crosstab('select produkid, warehousename, onhand
> from test order by 1,2') as ct (row_name text, categori_1 text,
> categori_2 text)
>
> Result : ERROR:  return and sql tuple descriptions are
> incompatible

Great -- thanks.

First of all, I suspect you do not want to use this form (the single
argument form) of the crosstab function. See:

  http://www.postgresql.org/docs/9.3/interactive/tablefunc.html

Specifically:
"The crosstab function produces one output row for each consecutive
group of input rows with the same row_name value. It fills the output
value columns, left to right, with the value fields from these rows.
If there are fewer rows in a group than there are output value
columns, the extra output columns are filled with nulls; if there are
more rows, the extra input rows are skipped."

- - and -

"The main limitation of the single-parameter form of crosstab is that
it treats all values in a group alike, inserting each value into the
first available column. If you want the value columns to correspond to
specific categories of data, and some groups might not have data for
some of the categories, that doesn't work well."

Therefore I would be willing to bet what you really want is something
like:

select *
from crosstab
(
  'select produkid, warehousename, onhand
   from test order by 1,2',
  'select distinct warehousename from test order by 1'
)
as ct (produkid text, office int, store2 int);
   produkid    | office | store2
- ---------------+--------+--------
 2791404000014 |     10 |     45
 2791404000021 |        |     10
(2 rows)

- - or -

select *
from crosstab
(
  'select warehousename,produkid, onhand
   from test order by 1,2',
  'select distinct produkid from test order by 1'
)
as ct (warehousename text, p_2791404000014 int, p_2791404000021 int);
 warehousename | p_2791404000014 | p_2791404000021
- ---------------+-----------------+-----------------
 OFFICE        |              10 |
 STORE2        |              45 |              10
(2 rows)

The second SQL provides the "category". It is generally useful to run
this from your app first, get a list of the actual categories based on
current data, and then have the app write the crosstab query
dynamically and execute it. In that case the last example might
actually be written as:

select *
from crosstab
(
  'select warehousename,produkid, onhand
   from test order by 1,2',
  'values (2791404000014),(2791404000021)'
)
as ct (warehousename text, p_2791404000014 int, p_2791404000021 int);
 warehousename | p_2791404000014 | p_2791404000021
- ---------------+-----------------+-----------------
 OFFICE        |              10 |
 STORE2        |              45 |              10
(2 rows)

HTH,

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.14 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJTWpBQAAoJEDfy90M199hlezsP/0cXv+ES1G68HXG2zONgZNT7
fcF665ZlofNYfKmzAy9KH9OuIeyGVO3tAd2So+xv42hZKca/M4FKVDd9bRqLlRZO
56ecfBGQIHFAQPyHk1pz4cddpHT+3NU5Ub40R/E+VoY6XkdBcaHRGEPMtDNoMm51
qHQpDV9vTsYAc3QZTamnBQ5IyPl1m2QUEeUq4y7IX69I9J/4+D8nprSzJm2ILHVX
RBjIydSZRjL8RabYcHA32HWX4zJJwekdybutgVXws3HBP8YkJcsV/flsqAu6kO0M
24V2lqA/aQbG2ggID3I99CE8MVNWpybV+vLEUUfwMMTPql/4GQF5GdlaZD9kkfp5
vGj9kHQIrW67JyoEAck/Nv0rjlwFAKXFqMi2XNR5MCfXIqJhKmIwk7jXTEE1jkP3
s6VCcvQTTGUtqZyyGq1bqCQ7/d71G4VAthxrYLz/ZajJ8N2HIfbcvB9LbAS4Qig7
i401yCzaGo9SuALKWN7pRZzI6aJZrcBshbvBSqFZQdWNlpbbaDoPwEgQ3MLT+7Xp
NdFF8HokrwNFQ416cUYVfi84s0whSzjHt4iZprUxhYCesvZgDnE8LUlJhz7aeiBD
3Xi+YDE9MqDrQPYNP5wXu2TsNTXQzyol7jo2fpvRbjs6T2B40Jc+SERGr2gUPrpF
bK4ejASGoW7z6uJUJwWd
=WSMX
-----END PGP SIGNATURE-----