Re: [SQL] returning composite types.

[Franco Bruno Borghesi]

yes, i was trying to do something like that, but it breaks always in the same 
place, first I thought that it was because of the way I was assigning values 
to the fields of my row, but now I'm beginning to think that the reason is 
the way I pass the row to f2. 

Here is the error:
franco=# SELECT f1();
WARNING:  Error occurred while executing PL/pgSQL function f1
WARNING:  line 5 at select into variables
ERROR:  Attribute "result" not found

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

CREATE OR REPLACE FUNCTION f1() RETURNS mytype AS '
DECLARE   
   result mytype%ROWTYPE;
BEGIN
   result.val1:=1;   
   SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);
   RETURN result;  
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION f2(mytype) RETURNS mytype AS '
DECLARE   
   arg ALIAS FOR $1;
   result mytype%ROWTYPE;
BEGIN
   arg.val2:=2;
   arg.val3:=3;
   SELECT arg.val1, arg.val2, arg.val3 INTO result.val1, result.val2, 
result.val3;
   RETURN result;   
END;
' LANGUAGE 'plpgsql';

what do you guys think?

On Saturday 29 March 2003 13:49, Joe Conway wrote:
> Franco Bruno Borghesi wrote:
> > ok, soy you're telling me that the only way to return a composite type is
> > using a set of them, even if I know my function will allways return 1
> > record.
>
> Try this:
>
> create type foo as (f1 int, f2 text);
> create or replace function retfoo(int, text) returns foo as '
>   declare
>result foo%ROWTYPE;
>   begin
>select into result $1, $2;
>return result;
>   end;
> ' language 'plpgsql';
>
> regression=# select * from retfoo(2,'b');
>   f1 | f2
> +
>2 | b
> (1 row)
>
> Joe


pgp0.pgp
Description: signature

Re: [SQL] Stored procedures

[Franco Bruno Borghesi]

As far as I know, you always work with a ResultSet. 
If you know your stored procedures will always return an Integer and you don't 
wanna deal with the executeQuery and stuff every time, you could create a 
class with methods explicitly for accesing your stored procedures, for 
example:

assuming you have a pg function returning an INT, called countPeople(), you 
could do

public class MyStoredProcs {
   private static int executeAnyProc(Connection conn, String procName) throws 
SQLException{
  Statement stmt=conn.createStatement();
  ResultSet rs=stmt.executeQuery("SELECT * FROM "+procName+"()");
  rs.next();
  return rs.getInt(1); 
   }

   public static int countPeople() throws SQLException{
  return executeAnyProc("countPeople");
   }
};

You could add methods to access every stored procedure in your database (even 
returning other data types), and you would use it like this in your code:

...
   int count=MyStoredProcs.countPeople();
   // do something with the value
   if (count>100) {
...


hope this is what you were looking for.  
 

On Saturday 29 March 2003 17:35, Zodiac wrote:
> Thank you for help.
> Just one more question. Have i direct access to stored procedure?
> For example, i have procedure which returns Integer and i wanna to have
> ability to write such code " int var = ANY_CALL". Where ANY_CALL is a my
> procedure call.
> I meant must i do "executeQuery" only and after then parse Statement
> variable?
>
> Thank you.


pgp0.pgp
Description: signature