I asked this in the pgsql-novice list and was directed here. Hopefully
some of you have seen this problem before....
Here's the story.... We have a product that was developed for MsSQL. We
are changing (or trying to change) over to PostgreSQL in an ongoing
effort to get away from MS products. We have one workstation that this
port was done on, and we are now trying to deploy on other machines.
Environment is pg under cygwin, odbc, and for this test pgadmin II.
On the development/test machine (pg 7.2.3, odbc 7.1.9, pgadmin 1.2.0),
you can put a query inside begin/end statements and get the results. e.g.
begin;
select * from table_name;
end;
returns the table contents.
We have several other machines, running various combinations, e.g.
pg 7.3.1, odbc 7.2.5, pgadmin 1.4.12
pg 7.2.1, odbc 7.2.3, pgadmin 1.2.0
and using the same SQL with those we just get a "query OK" message. It
obviously does the query (I can put a delete from... within the
begin/commit and it deletes), but it doesn't display the results.
Which behavior is "right", and is there something we can do to see the
results?
?Additionally? our application has a function that returns a cursor with
a result set. This works with the first system (the development system
this was coded on) but we can't get a similar behavior to work on the
platform we want to deploy/develop on now. This is actually the first
problem that we ran into and we discovered the query-within-transaction
behavior difference in trying to troubleshoot that.
Alternatively, is there another way to return a set of rows/columns from
a function? The MsSQL app had a stored procedure that returned several
results all at once. This was no problem in MsSQL, where a stored
procedure can return a table structure without any special code, but
seems to require explicit cursor use in postgresql?
Thanks in advance!
-Jon