Foreign Database Connectivity

mlw <pgsql@mohawksoft.com> writes:

Any thoughts?

See previous discussion of SQL-MED standard. I'd prefer to see us try
to implement a standard than invent something off-the-cuff ...

regards, tom lane

I have been thinking about this for a while. Now that we have a
practical methodology for returning full rows from functions, we could
make this sort of thing pretty "easily."

Does postgres now support returning multiple rows / tuples from a stored
procedure / function?

Tom Lane wrote:

mlw <pgsql@mohawksoft.com> writes:

Any thoughts?

See previous discussion of SQL-MED standard. I'd prefer to see us try
to implement a standard than invent something off-the-cuff ...

Does anyone implement this? I have never seen it before. What about
Oracle's Create database link syntax?

On Tue, 2003-04-15 at 20:04, Rob Butler wrote:

Does postgres now support returning multiple rows / tuples from a stored
procedure / function?

Yes (since PostgreSQL 7.3).

Cheers,

Neil

mlw <pgsql@mohawksoft.com> writes:

Tom Lane wrote:

See previous discussion of SQL-MED standard. I'd prefer to see us try
to implement a standard than invent something off-the-cuff ...

Does anyone implement this?

Couldn't say. It's a relatively new standard. That hasn't stopped us
from implementing SQL99 stuff though, nor in some cases SQL200x stuff
that the ink is still wet on...

What about Oracle's Create database link syntax?

What about it? Does anyone beside Oracle support it? Can we implement
it without running afoul of Oracle patents? Quite honestly, I'm afraid
to base any feature on "this is how Oracle does it", first because it's
foolish to shoot at a target that the competition can move at will, and
second because I know that sooner or later they are going to be looking
for ways to nail us for patent infringement.

regards, tom lane

Tom Lane wrote:

What about Oracle's Create database link syntax?

What about it? Does anyone beside Oracle support it? Can we implement
it without running afoul of Oracle patents? Quite honestly, I'm afraid
to base any feature on "this is how Oracle does it", first because it's
foolish to shoot at a target that the competition can move at will, and
second because I know that sooner or later they are going to be looking
for ways to nail us for patent infringement.

A real concern for sure. OK, but...

Supporting the SQL/MED syntax will take *a lot* of work, where as a
simpler PG-Only feature can be developed as a contrib. I think there is
a *need* for the ability, but not nessisarily a requirement for a
specific implementation.

So, lets assume that the core PG crowd hates what ever it is that would
get built to do this. No worries, its not the first time :) Lets forget
that it is a feature that you do not like, and I am asking for a more
"generic" feature for functions returning sets.

How about this: (a varient)

CREATE [OR REPLACE] FUNCTION name (args)
RETURNS setof (mycol1 integer, mycol2 varchar)
LANGUAGE langname
.....
WITH (attribute, param1='param1', param2='param2',...)

Now, what would be cool, is if there was a way for the RETURNS specifier
to be passed to the function in some easy to use preparsed form. So that
the function could "know" what it was supposed to return and the name of
the field, it is important for the function to know the data type and
its name.

The "WITH" attributes could provide one more attribute, a parameter
which could be passed to the function. his will allow functions to do
virtually anything, for instance:

create function "ODBC_Music" (varchar) returns setof (id integer, title
varchar, artist varchar)
as 'mydll.so', 'ODBC_Music'
LANGUAGE C
WITH(STABLE, param1='NAME:DBUSER;DSN:FREEDB;AUTH:FUBAR', param2='select
* from sometable where title = ''%s''');

Would this implementation take much *any* real work? If PostgreSQL had
this, then external DB access would be trivial to implement. More
importantly, it makes it easier for a DBA to use an ISV's data link
function. Also, the "RETURNS" parameter could/should be he standard
CREATE TABLE syntax.

mlw <pgsql@mohawksoft.com> writes:

How about this: (a varient)

CREATE [OR REPLACE] FUNCTION name (args)
RETURNS setof (mycol1 integer, mycol2 varchar)
LANGUAGE langname
.....
WITH (attribute, param1='param1', param2='param2',...)

Now, what would be cool, is if there was a way for the RETURNS specifier
to be passed to the function in some easy to use preparsed form.

Just create a rowtype and declare the function as returning that.

With Joe's recent additions for polymorphic functions, it's even
possible for the function to discover what it's supposed to return
at runtime. (Hey Joe, did we make that work for functions called
from the FROM clause? If not, seems like something to fix up.)

The "WITH" attributes could provide one more attribute, a parameter
which could be passed to the function.

Actually, the way that you probably ought to build it is as a new PL
language type. All the stuff you are thinking of as WITH parameters
would be inside the "function body" in some trivial syntax. I think
this could likely even be built in 7.3, without the polymorphic
functions (PL handlers are already polymorphic ...)

regards, tom lane

Tom Lane wrote:

mlw <pgsql@mohawksoft.com> writes:

Now, what would be cool, is if there was a way for the RETURNS specifier
to be passed to the function in some easy to use preparsed form.

Just create a rowtype and declare the function as returning that.

Or for flexibility use "RETURNS setof record"

With Joe's recent additions for polymorphic functions, it's even
possible for the function to discover what it's supposed to return
at runtime. (Hey Joe, did we make that work for functions called
from the FROM clause? If not, seems like something to fix up.)

Yeah -- the best example is the new hash based crosstab function in
contrib/tablefunc:

CREATE OR REPLACE FUNCTION crosstab(text,text)
RETURNS setof record
AS 'MODULE_PATHNAME','crosstab_hash'
LANGUAGE 'C' STABLE STRICT;

create table cth(id serial, rowid text, rowdt timestamp,
attribute text, val text);

<insert data>

SELECT * FROM crosstab
(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS (rowid text, rowdt timestamp, temp int4, res text,
startdate timestamp, volts float8);

In this case crosstab_hash() gets its tupdesc from rsinfo->expectedDesc.
It then uses the output of SPI_getvalue and the tupdesc to build the new
tuple via BuildTupleFromCStrings()

The "WITH" attributes could provide one more attribute, a parameter
which could be passed to the function.

Actually, the way that you probably ought to build it is as a new PL
language type. All the stuff you are thinking of as WITH parameters
would be inside the "function body" in some trivial syntax. I think
this could likely even be built in 7.3, without the polymorphic
functions (PL handlers are already polymorphic ...)

Sounds like an interesting approach -- actually PL/R uses the
polymorphic abilities pretty extensively too. For example when the R
function returns a "data frame", PL/R uses the runtime return type to
decide what to do with it.

As far as foreign database connectivity goes specifically, someone has
already done a proof of concept "jdbclink" based on dblink (which he
sent to me). If I can find the time before the 7.4 feature freeze, I'm
going to try to merge his code into dblink so that dblink can access any
jdbc data source. Beyond that, I think heading down the SQL-MED road is
the way to go.

Joe

Joe Conway wrote:

Tom Lane wrote:

With Joe's recent additions for polymorphic functions, it's even
possible for the function to discover what it's supposed to return
at runtime. (Hey Joe, did we make that work for functions called
from the FROM clause? If not, seems like something to fix up.)

Yeah -- the best example is the new hash based crosstab function in
contrib/tablefunc:

Dooh! I just reread what you wrote. Your referring to the FuncExpr
addition to the FmgrInfo struct. I'll have to check that out and let you
know. I'm getting ready to dive back into that stuff in the next day or so.

Joe

Tom Lane wrote:

With Joe's recent additions for polymorphic functions, it's even
possible for the function to discover what it's supposed to return
at runtime. (Hey Joe, did we make that work for functions called
from the FROM clause? If not, seems like something to fix up.)

I'm just getting back into the polymorphic-functions/array-expression
changes this weekend. To follow up on the question above, I checked and
found that get_fn_expr_rettype() works fine in FROM clause functions.

Of course, as I mentioned earlier, fcinfo->resultinfo->expectedDesc is
also available in FROM clause functions, and is probably more convenient
to use for return type discovery.

However, get_fn_expr_argtype() is your only real option as far as
discovering run time argument types. I'm sure it also works in FROM
clause functions (because I'm actively using it in plr)

Joe