Function not matching

[0x6e6562]

I've installed the pivotmytable into my DB instance, and I've created the example myinfo table, but for some reason I can't execute the query:

# select * from pivotmytable('myinfo','pivotedinfo','player,tool','round','sum');
ERROR:  function pivotmytable(unknown, unknown, unknown, unknown, unknown) does not exist
LINE 1: select * from pivotmytable('myinfo','pivotedinfo','player,to...
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Do you have any ideas about how to debug this?

[0x6e6562]

So the issue was the calling convention. This is how I got it to work:

select * from pivotmytable('myinfo','foo','player','round','hits','sum',false,'asc',false,false);

[jtornero]

Thank you very much for the feedback. Sorry I was out for a while. I'll check it out because it is supposed to work as I show in the page.

[jtornero]

Dear Ben: I've realized that the web page for pivotmytable had an error regarding the invocation of the function, so issuing the query like I suggested leads to the error you've pointed out.

I've updated the web page at http://imasdemase.com/programacion-2/tablas-cruzadas-en-postgresql-pivotmytable/ and now it shows the proper way of calling the function. For our example, it should be:

    select * from pivotmytable('myinfo','pivotedinfo','player,tool','round','hits','sum',sort_order:='asc');

In my wrong example, I forgot to include the _valuefield in the parameters list (for our example case, it's the field hits of the table myinfo)

Thank you very much for your feedback.

Jorge Tornero

[0x6e6562]

Thanks for clarifying this. BTW, is it necessary to return the results into a predefined table or can you return a regular result set?

[jtornero]

Well, AFAIK it is not possible to return a regular result set, like those you get when issuing a select * from foo in psql. Bear in mind that we must declare de return type of the function in its declaration and it must contain the resulting column list. It is impossible to do before all the operations of the function are done. We can return the results to screen but not with in a fancy, formatted way as psql does. At least, with my current PostgreSQL/plpython level of knowledge.

I've been able to return a dictionary-like thing like:

 pivotmytable                                                                                                                                                                                                                              
-----------------
 [{'Rd5': 0, 'Rd4': 0, 'Rd1': 0, 'Rd3': 0, 'Rd2': 3, 'tool': 'Hammer', 'player': 'Manu'}, {'Rd5': 0, 'Rd4': 0, 'Rd1': 28, 'Rd3': 0, 'Rd2': 0, 'tool': 'Wrench', 'player': 'Manu'}, {'Rd5': 0, 'Rd4': 0, 'Rd1': 12, 'Rd3': 0, 'Rd2': 17, 'tool': 'Hammer', 'player': 'Pepito'}, {'Rd5': 1, 'Rd4': 0, 'Rd1': 0, 'Rd3': 0, 'Rd2': 0, 'tool': 'Wrench', 'player': 'Pepito'}, {'Rd5': 0, 'Rd4': 22, 'Rd1': 34, 'Rd3': 42, 'Rd2': 15, 'tool': 'Hammer', 'player': 'Richal'}]
(1 row)

making the function to return a refcursor, but I think it is pretty useless or, at least, I don't know hot to use it in any way. Maybe we need a real expert in these matters :)

Best regards,

Jorge Tornero

[0x6e6562]

Thanks for the heads up.