Re: ADO and PostgreSQL functions

"Zlatko Mati" <zlatko(dot)matic1(at)sb(dot)t-com(dot)hr> wrote in message
news:d3tarq$s98$1(at)ss405(dot)t-com(dot)hr(dot)(dot)(dot)
> Hello.
>
> While I was working with Access Projects (Access front-end with MSDE) I
> was able to call stored procedures by using ADO command and parameters
> object.
> Now I am trying to migrate my database from MSDE to Postgre and I'm
> wondering about stored procedures on Postgre...I couldn't find such
> expression in Pg documentation, so I suppose that there are no such thing
> on Postgre. On the other hand, I could see that functions could play such
> role. Am I right?

Yes. Functions can be written to take pretty much whatever parameters you
want, and can return a value, a row, a resultset, or nothing if you wish.
You also have a wide choice of languages to use. The documentation has
plenty more on this.

> If that is so, how can I call such functions from the client (MS Access,
> for example) ? I couldn't call MSDE functions from MS Access using ADO,
> because ADO recognize only stored procedures (adCmdStoredProc), so I
> suppose that the same problem could be with Postgre functions...
> How can I use Postgre functions as recordset for my forms and reports in
> MS Access? How can I call and execute parameterized functions?

The usual syntax for calling functions is "select myfunction(param1,
param2...);". For set returning functions, you need to write "select * from
myfunction(param1, param2...);". Note that a function has to return a
datatype recognised as such by the database. Every table automatically has
created a type which corresponds to its rowtype; you can also create any
type you want.

In Access, there are two approaches you can use to run a paramaterised
query, with the parameters set at runtime:
1. Construct a query string using the supplied parameters, and then
explicitly set the SQL property of your querydef to that string, or:
2. Send the query string to the backend as a passthrough query.

You may have trouble getting an updatable recordset this way (certainly with
#2), which will give you trouble updating when using bound controls. But if
you don't mind writing some code, you can handle updates/inserts easily
enough in your app. You may find that using SQL for your updates and
inserts, rather than ADO methods, is more robust.

Some versions of the ODBC driver have trouble with queries using set
returning functions, because they don't recognize that the function name
does not apply to a table. I believe that this has been fixed in recent
driver versions, but I haven't tested this. This problem would not apply in
any case to passthrough queries.

I suggest that you search the archives of the pgsql-odbc list for
information, and direct any further questions to that list, where you are
more likely to get good answers.
HTH

>
> Thanks.
>
>