tag:blogger.com,1999:blog-8839574367290288724.post7449458148004287481..comments2023-12-11T13:13:11.330-08:00Comments on Pavel Stehule's blog: Using cursors for generating cross tablesPavel Stěhulehttp://www.blogger.com/profile/01996484227228696817noreply@blogger.comBlogger15125tag:blogger.com,1999:blog-8839574367290288724.post-67166164211550579812012-08-01T01:31:18.676-07:002012-08-01T01:31:18.676-07:00hi there . your blog has helped me alot thank you ...hi there . your blog has helped me alot thank you very much . now i need some altretaion in this besause my requirmnts are slightly different . my scenario is i have three columns say <br /> A(text) B(text) Value(text) <br /> AA1 AA1 0<br /> AA2 AA1 100<br /> AA3 AA1 100<br /> AA1 AA2 100<br /> AA2 AA2 0<br /> AA3 AA2 100<br /> AA1 AA3 100<br /> AA2 AA3 100<br /> AA3 AA3 0<br /><br />now i want a cross table that looks like <br /> AA1 AA2 AA3 <br />AA1 0 100 100<br />AA2 100 0 100<br />AA3 100 100 0 <br /><br />and then it should replace o with not applicable (N/A) so i am looking a cross table like<br /> <br /> AA1 AA2 AA3 <br />AA1 N/A 100 100<br />AA2 100 N/A 100<br />AA3 100 100 N/A <br /><br />i have achieved the table with zeros but i am unable to place N/A value where row and column name is same i,e fro AA1 to AA1 or AA2 to AA2 . thans i will wait for your replyAnonymoushttps://www.blogger.com/profile/15401737637236368531noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-68169817244386310792012-08-01T01:01:02.878-07:002012-08-01T01:01:02.878-07:00Hi Pavel Stěhule
its a beautiful code to create c...Hi Pavel Stěhule <br />its a beautiful code to create cross relation pivot table. i need to change all diagonal values to 'N/A'. i have been trying to make some changes but could not succeeded. can you plz tell me how can i do this.Peternoreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-59541457669309201062012-06-20T01:32:28.109-07:002012-06-20T01:32:28.109-07:00To Adriano: You can use aggregate function with cu...To Adriano: You can use aggregate function with cursors - but you cannot nested aggregates - if you can understand code - it is based on dynamic SQL - then some query is dynamically created - and it use SUM as example. When you use COUNT(*), then it creates query with ..SUM( .. COUNT(*)) that is not valid. If you can use COUNT, then you have to modify source code. /parameters - it is column name and source, column name and source.Pavel Stěhulehttps://www.blogger.com/profile/01996484227228696817noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-59312420525751025292012-06-18T22:23:49.613-07:002012-06-18T22:23:49.613-07:00well, I tried the following:
SELECT do_cross_curs...well, I tried the following:<br /><br />SELECT do_cross_cursor( 'varid', <br /> 'FROM loggingdb_ips_boolean As log',<br /> 'to_char(ipstimestamp, ''mon DD HH24h'')',<br /> 'FROM loggingdb_ips_boolean As log',<br /> 'COUNT(*)::integer');<br />FETCH ALL FROM result; <br /><br />But it aborts with the following message: <br />ERROR: aggregate function calls cannot be nested<br />SQL state: 42803<br />Context: PL/pgSQL function "do_cross_cursor" line 19 at OPEN<br /><br />Does this mean that one cannot use aggregate functions within this cursor?Prof. Adriano Aguzzi, MD PhD DVM h.c. FRCP FRCPathhttps://www.blogger.com/profile/04846009246734498726noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-2883770441397804852012-06-18T22:07:11.395-07:002012-06-18T22:07:11.395-07:00Dear Pawel
thank you for your help. When I paste t...Dear Pawel<br />thank you for your help. When I paste the function into pgAdmin without wrapping it into the transaction, it works fine. Within the transaction block, it rejects the output. I will try to read more about transactions in order to understand this behavior. <br />I have a big question though. Could you write a very short (3-4 lines) documentation of the parameters of your function? What is the admissible parameter syntax, etc? A million thanks in advance!!!Prof. Adriano Aguzzi, MD PhD DVM h.c. FRCP FRCPathhttps://www.blogger.com/profile/04846009246734498726noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-77399170207310672772012-06-11T23:33:49.989-07:002012-06-11T23:33:49.989-07:00To Adriano: you can test it in console? What are y...To Adriano: you can test it in console? What are you doing exactly? This is pgAdmin message or PostgreSQL message?Pavel Stěhulehttps://www.blogger.com/profile/01996484227228696817noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-74995614791834880422012-06-11T21:23:13.441-07:002012-06-11T21:23:13.441-07:00I am a molecular biologist and have minimal Postgr...I am a molecular biologist and have minimal Postgres knowledge, so my question is very naive. I pasted your code into pgAdmin sql window, and everything works fine, your function is being created. But when I execute your procedure, PG says "3 rows discarded" and shows nothing. I am sure that this is because of my ignorance - can you tell me how to do it right? My goal is to create simple dynamic pivot queries like with Access.Prof. Adriano Aguzzi, MD PhD DVM h.c. FRCP FRCPathhttps://www.blogger.com/profile/04846009246734498726noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-64988234816176141892010-07-19T18:29:06.273-07:002010-07-19T18:29:06.273-07:00Brilliant article... it is simpler to do crosstab ...Brilliant article... it is simpler to do crosstab ;;)<br /><br />Best regards,<br />Irmanirmanhttp://irmant.blogspot.comnoreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-90451193468583872482010-02-12T04:58:23.414-08:002010-02-12T04:58:23.414-08:00This is in my ToDo (when I meet some sponsor). It ...This is in my ToDo (when I meet some sponsor). It is standardized: CREATE PROCEDURE and CALL statement.Pavel Stěhulehttps://www.blogger.com/profile/01996484227228696817noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-3007778761026414792010-02-12T04:45:32.317-08:002010-02-12T04:45:32.317-08:00How about implementing a new keyword in Postgres, ...How about implementing a new keyword in Postgres, like EXECUTE PROCEDURE 'function_name()' which returns "SETOF RECORD", and which is equivalent to SELECT * FROM 'function_name()', but doesn't give you an error?<br /><br />regards,<br />davorAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-87859970763564893612009-07-07T22:38:23.667-07:002009-07-07T22:38:23.667-07:00To Gustavo
Hello
what I know, this method is the...To Gustavo<br /><br />Hello<br /><br />what I know, this method is the best on SQL level. But on very large tables this query should run long time. PostgreSQL missing OLAP or ROLAP support. Try it and you will see. This solution is very simple, but if it will be too slow you can use some general OLAP sw - Mondrian, Pentaho<br /><br />regards<br />PavelPavel Stěhulehttps://www.blogger.com/profile/01996484227228696817noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-42491199945136525402009-07-07T16:11:14.588-07:002009-07-07T16:11:14.588-07:00Hi!
Very good article! It's just what I'v...Hi!<br /><br />Very good article! It's just what I've looking for.<br /><br />I have one question: probably I'll use this technique in an application I'm developing and want to know about the impact in terms of performance with a large ammount of data. Is it fine?<br /><br />Thank you!Gustavo Straubehttps://www.blogger.com/profile/03355680668513965685noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-9203510180186563692009-02-18T12:15:00.000-08:002009-02-18T12:15:00.000-08:00Your article was very helpful to me, thanks a lot ...Your article was very helpful to me, thanks a lot :-)<BR/>wAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-8628424775927030852008-08-26T04:07:00.000-07:002008-08-26T04:07:00.000-07:00Hello,I reformated article. Wrong style :(Hello,<BR/><BR/>I reformated article. Wrong style :(Pavel Stěhulehttps://www.blogger.com/profile/01996484227228696817noreply@blogger.comtag:blogger.com,1999:blog-8839574367290288724.post-83001719319048990652008-08-26T02:49:00.000-07:002008-08-26T02:49:00.000-07:00At least on Firefox, the SQL text in the page is t...At least on Firefox, the SQL text in the page is truncated. May it be possible to fix the CSS or to wrap the text so it is readable ?<BR/><BR/>Thank you for your valuable work on postgreSQLLuca Veronesehttps://www.blogger.com/profile/10889674744901410391noreply@blogger.com