Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Unanswered: how to store the result set of one function into a variable of another

    I have written a function :-

    create or replace function f_cust(text) returns setof int as '
    declare ips alias for $1;
    len int; pos int;op int;ip text;
    begin
    ip := ips;
    loop
    len := length(ip);
    pos := position('','' in ip);
    if (pos > 0) then
    op := cast(substr(ip,1,pos-1) as int);
    ip := substr(ip,pos+1,len-pos);
    end if;
    if (pos =0) and (len > 0) then
    op := cast(ip as int);
    end if;
    return next op;
    exit when pos = 0;
    end loop;
    return;
    end;'language plpgsql;


    the i/p to this function :-

    select * from f_cust ('1,2,3,4);

    the o/p is :-
    1
    2
    3
    4

    i.e it takes a string i/p and converts into integer values which can be used by another function to compare values.


    The other function is :-


    create or replace function f_Emp(text)
    returns setof record as '
    declare cusid alias for $1;a ???; --what should be the data type ??
    rec record;
    begin
    select * into a from f_cust(cusid);
    for rec in select * from customer where customerid in ($1) Loop
    return next rec;
    end loop;
    return;
    end;' Language plpgsql;


    how do i store the value of one function into another functions variable so that it can be used for comparison purpose.

    Is there a better way to write the function ?

    Any help would be highly appreciated.

    thanks

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400
    I don't understand your question

    if you have

    create or replace function f_Emp(text)
    returns setof record as '
    declare cusid alias for $1;a ???; --what should be the data type ??

    then cusid is of type text
    $1 is the fist argument of the function

    if you get int back from the other function use int
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    Apr 2004
    Posts
    49

    Question

    Hi,
    Sorry i am not talking of the data type for cusid, i am talking of the data type for a , which ideally should be taken int ... but then how do i store setof int in a single variable ... so that it can be compared with the column of the customer with the data type int.

    Cause , sorry if i am ignorant , but i believe that as far as i know variables can store only 1 value how do i store more than 1 value ..so that it can be compared in the where clause of the query down below.

    I know it has to be simple ..... but right know i am not getting it. Pls help me out in this.

    Thanks,

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    i understand

    you need to work with data type RECORD
    with represents a row
    and I think you can solve this problem with table functions
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •