Re: [SQL] Stored procedures

[Franco Bruno Borghesi]

Here is a full example of a java program showing the data from a set returning 
function:

-
--IN YOUR DATABASE
CREATE TABLE people (name TEXT);
INSERT INTO people VALUES ('john');
INSERT INTO people VALUES ('peter');
INSERT INTO people VALUES ('joe');

CREATE FUNCTION getPeople() RETURNS SETOF people AS '
DECLARE
   rec RECORD;
BEGIN
   FOR rec IN
  SELECT name FROM people
   LOOP
  RETURN NEXT rec;
   END LOOP;   
   RETURN;
END;' LANGUAGE 'plpgsql';

---
--ListPeople.java
import java.sql.*;
public class ListPeople {
   public static void main(String[] args) {   
  try {
 Class.forName("org.postgresql.Driver");
 Connection 
con=DriverManager.getConnection("jdbc:postgresql:franco?user=admin");
 Statement stmt=con.createStatement();
 ResultSet rs=stmt.executeQuery("SELECT * FROM getPeople()");
 while (rs.next()) {
System.out.println(rs.getString("name"));
 }
  }
  catch (Exception e) {
 System.out.println("Exception: "+e.getMessage());
  }
   }
}

On Friday 28 March 2003 19:31, Zodiac wrote:
> Hello!
> Can anybody tell me one thing.
> How can i call stored procedures in my java-programm?
>
> Thanks for any help.


pgp0.pgp
Description: signature

Re: [SQL] returning composite types.

[Jordan S. Jones]

Here is how I have been doing mine:

CREATE FUNCTION "myFunction" () RETURNS SETOF mytype
AS
'
DECLARE
    r       mytype%ROWTYPE;
BEGIN
    FOR r IN [SELECT STATEMENT]
    LOOP
        RETURN NEXT r;
    END LOOP;
    RETURN;
END;
'
LANGUAGE 'plpgsql';
  
Hope this Helps..

Jordan S. Jones


Franco Bruno Borghesi wrote:

  Hi guys.

I'm working with functions in my database, using plpgsql, but I reached a 
point where I realize I'm missing a concept: how do I return composite types 
from a function? I'll give you an example:

CREATE TYPE mytype AS(
   val1 INTEGER,
   val2 INTEGER,
   val3 INTEGER,
   val4 INTEGER   
);

If I want my function to return a "mytype" type, should I declare it as:
CREATE FUNCTION myFunction() RETURNS mytype AS ...
or maybe
CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...

and in any case, inside the function, how should I declare the variable 
holding the return value?:

DECLARE
   result mytype;
BEGIN
...
   RETURN result;   
END;   

or maybe  

DECLARE
   result mytype%ROWTYPE;
BEGIN
...
   RETURN result;   
END;   

I've read the documentation and the examples in it, but I still don't 
understand what the right way is. If you could give an example of a function 
filling "mytipe" and returning it, it would really help me.

Thanks in advance.