[Pljava-dev] setof record

[Roberts, Jon]

Here is a sample pl/pgsql function that returns a "setof record".

create or replace function fn_test() returns setof record as
$$
declare
  v_rec record;
begin
  
  for v_rec in select cast(1 as int) as col1 
               union 
               select cast(2 as int) loop

    return next v_rec;

  end loop;
end;
$$
language 'plpgsql';


To use this, I would execute it as:

select * from fn_test() as (col1 int);

The trick is I must specify the columns with the appropriate data types.


I would like to take this basic concept and create a way to connect to a
remote database like Oracle or SQL Server and get data using pl/java.  

The pl/java function would accept parameters like server_name, port,
user_name, password, and sql_statement and return a setof record.  At
execution time, I would figure out based on the select statement I'm
passing to the function what the columns are in the remote database.

Something like:

select * from fn_remote_sql(v_server_type, v_server, v_port, 
                            v_username, v_password, 
                            'select name, address from customer') as 
                           (name varchar, address varchar);

Then the next time I might execute:
select * from fn_remote_sql(v_server_type, v_server, v_port, 
                            v_username, v_password, 
                            'select max(id) as max_id from customer') as

                           (max_id integer);


So my question is, how do I return a setof record as my pl/pgsql
function does but from Java?



Jon