Thread: tablefunc extension

Hi Folks,

The following query appears to be correct but fails to execute.

\set VERBOSITY verbose

select * from crosstab('select claim_id::bigint, patient_id::integer, code_=
id::text from diagnosis') as dg_list(claim_id bigint, patient_id integer, c=
ode_id text);

ERROR:  42601: return and sql tuple descriptions are incompatible
LOCATION:  crosstab, tablefunc.c:445

The above query works if patient_id is cast to text.  The claim_id as bigin=
t works too.

postgres   9.2.4
openSUSE 12.3 (x86_64)
VERSION =3D 12.3


diagnosis
--------------+--------------+---------------------------------------------=
----------------------
id            | integer      | not null default nextval('diagnosis_id_seq':=
:regclass)
patient_id    | integer      | not null
code_id       | text         | not null
claim_id      | bigint       |
 seq_num       | smallint     |
 poa_indicator | character(1) |



Thanks for your time and help.

Carl Clemens




The information transmitted is intended only for the person or entity to wh=
ich
it is addressed and may contain confidential and/or privileged material. Any
review, retransmission, dissemination or other use of, or taking of any act=
ion
in reliance upon, this information by persons or entities other than the
intended recipient is prohibited. If you are not the intended recipient of =
this =

message, please contact the sender and delete this material from this compu=
ter.

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

On 09/19/2013 12:24 PM, Carl Clemens wrote:
> The following query appears to be correct but fails to execute.

Your example is incorrect, however it is not clear (to me at least)
from your example what you are expecting as output -- please see the
documentation:

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

If you still have a problem please provide SQL that can be cut and
pasted which:
1) create your table
2) insert sample rows
Then show us what out put you are trying to acheive as output from
that data.

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.12 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJSP3qGAAoJEDfy90M199hljPoP/R/LPHwlkQYtIE9YjNS1FRoJ
viivrvUzAeP27zivgo8rIIrfXNyghUTfE8dyBRL1JB934PLcyu83+J9fcfW979xO
ogt2J4JJ3pctfOOm0cMdlYDarZPeo4VGXFQMe1K/7qMSZJGLUWbGwlR8pF2o/AGa
JdIMxwWuv6DwmU7yt8YnwWYRuyScUPyBFJCqntcrlfIDDlB313fdSUpu55c5/c89
Nq6+OGWScN9hA5AGm6AeucGEs8g3aNuevKN+AA6BN0D1wuo+pvkdK+iI9MlNyYlD
cB+i7+oluSb+UkNNcvz+PW0/3D0CJ12/nZfhOFsJCoplYzbuPJbZfI9eA/d9r8E3
YdDbN1/ofVAHLKx8Wy9LKqsGDb0pLFCTxc+jZhRkR87qf9qJ1OAb/DJHj7b9A0EC
/xy5LoxAFF61yGZjK9dPllF/asJcJkm3074G+hfCG5NdlJdOzshQ6eSNZqwOSTOM
6TPfQNTkpGmFfuuKRYTQjjAC+rUO6rfD+OIA+02rhYuRA1d7bxQw/tZvhOA9ZwvT
GVfpKHNnbG72euY8q9NMuamoImy1ivVdXkt1kkKKQkXi4UGwyRhS08Rf+JVfP5Zz
rNNtFp8lAo/eo5C3P5vFGUMr/4hcvjnPV71ZjgN4dG4Gp/HWdUsXn09TUrr1xV27
gcDhB2QFu+vbuP4yG/+N
=8/9U
-----END PGP SIGNATURE-----

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

On 09/22/2013 04:17 PM, Joe Conway wrote:
> If you still have a problem please provide SQL that can be cut and
> pasted which: 1) create your table 2) insert sample rows Then show
> us what out put you are trying to acheive as output from that
> data.

Oh, and switch to the pgsql-general list, as there is no evidence of
any bug here.

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.12 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJSP357AAoJEDfy90M199hlzyAP/1uVXwa9twDS6vB5EKiXbM6J
FSrktrUzaQGA47onFZ6JKFjCSy4LIhaY7bev+VIW5m9hxVT5bNXe6uPqf6QT+pdK
CevU7M29FUIB/ncF62aNYMIdjsM7yXqh+ppSQjqs2cs4jOWzn2BBxb1QCRezps2K
sHwOi5VfqKGcl/7Za/aX6ojzNfssgi9YGWF20c7IHhhhNAlXOlw6ARmnv98lsaS/
tVIUuzibVjAD36f0v80lx3+oOVv9H4IMDKfcRQ/c+V5RlGvo6ZxUiJYxRre5GAcw
1SllJ46kyGXxdhIWkPKBxF8/4E+QMc1rOqlLrUgj3j/1Ymua0YNlIL70M9tBcPIJ
idN/z+2jXYa8vHkLtpcjXTZGEhSEppCFOOw1sYEznqaSkVARabGptUsiKdGG8pll
D0FvKVzJRQeKWxdP0q5LwTF5BSz9KILT9vKPHcMz0aN9ddf25oDv9B5gyQECRxEf
BQYz0nULvz3YPAAaEIQRZehg6HBR7jM82dAu4Hnpm9la50rtmandefr9PliUMlXB
N3kkpnFQGvFOJpbOAvpSYsaJ2PxjPErlxe/0R5aJVMnpq/4E+1WDNAzm907sIsyb
raPFMY/0gEeqjVsZ2+o/fARj3FT1jlSkAKumo8Td0Q82bdisPgY7Bk6RqB3BDhEv
ZERlQeMkTiWESjB46W+f
=LUHW
-----END PGP SIGNATURE-----