Postgresql + ADO

[Artificial_Intelligence]

i have a function that returns a record , however ado stored procedure component is telling me i need a column definition list.
any ideas. more than likely its getting that info from the ODBC driver for Postgresql

[Blunty Killer]

Postgres usually returns that error if your function returns out parameters (column list) and no definition. This happens when you call the function as 'SELECT function()' instead of 'SELECT * FROM function().' When you use the latter, Postgres treats the result of the function as results of a normal query and return tuples as if it were from a table.

Could you try a query of the same function using the 'SELECT * FROM' method?

[Artificial_Intelligence]

When i do a query of the sort

select * from some_function the results are as expected
however when i run the stored procedure from Delphi using the ado stored procedure i get the error..

i changed the procedure and have not quite what i wanted but a working solution . Just so you know i switched from using the posgresdac to having an odbc souce and using the
ADO set of components.

[Blunty Killer]

Sorry to see you're still having issues with the query and parameters (PostgresDAC)? Did you try the test case I sent?

The problem I found with the ODBC and ADO setup is Performance; can be an extreme pain when running serious queries.

I have not dived too deeply in the ADO sources but I suspect it is using that style of calling convention for stored procs (SELECT stored proc). If it is the case then you will have to use a query because Postgres recommends using the 'SELECT * FROM' method for functions returning results.

[Artificial_Intelligence]

I know ill take a performance hit with the ODO setup, but just got tired of waiting for PDAC to work.........was kind of disappointed, since i am taking out a mysql server for a Postgresql server and to not even have the DAC working seems like a slap in my face. Considering i was the one who said yes lets take that one out and put this one in. In any case time is againts this project as its any upgrade (much needed ) so for now ill go with what works slow or not.

[Artificial_Intelligence]

weired thing is when i do rum the short version select som_proc in the console it works very well. but not in the application.

[Blunty Killer]

When returning results, if you use OUT parameters (the return type of the function automatically becomes a record) and you use the short version, you get a list of the values but not a row. For example, a function test() with two out parameters 'name' and 'id' will return this result '(name value, id value).' But this is not a true result set. Whereas SELECT * FROM will return:
name | id
----------
value | value

x rows fetched (y ms).

So you have to be careful with thinking you are getting a result set. With the first one you do have to declare the column definitions manually, and with the second, the parser does this for you automatically because the function then gets treated as a normal relation.

With regards to PostgresDAC, I still think that the issue you are having is with the double quotes. Sorry to see you're having so much issues with it, everyone I know is up and running perfectly. As a matter of fact, PG Lightening Admin from Tony Caduto uses PostgresDAC and it's a very fine program.

[Artificial_Intelligence]

I am sure PostgresDAC is quite kewl unfortunately time to figure the quirks out is time i don't have. i am sure there is something i am overlooking(missing) that keeps it from running the way it should. in the meantime i have to see how best i can get this application out the door. By the way i have hading another weired problem (just my luck) where is you use a table component from the ADO component set ....it will not pick up the index from the table assigned to it....

[Blunty Killer]

By the way i have hading another weired problem (just my luck) where is you use a table component from the ADO component set ....it will not pick up the index from the table assigned to it....

That is weird. Just did a test case and picked both index and foreign keys. What version of PG are you using? 8.0 + ?

[Artificial_Intelligence]

I am using version 8.2, well i was, switched to MSDE.