Results 1 to 5 of 5
  1. #1
    Join Date
    May 2006
    Posts
    14

    Unanswered: Creating function to return more than 1 tuple

    Hi,

    I am creating a function that extracts information from more than 1 table and displays it on the screen. I created a type for displayin the information n storing the return type of function. I declared the return type as setof TYPENAME, and i am returning next 'parameter_typeName'. My function only returns a single tuple though, i want it to display all tuples that satisfy the given conditions... Can someone plz tell me how i can display all tuples?? thnx!

    Here's what i did:
    create or replace function retrieveContent(varchar)
    returns setof contentinfo as '
    declare
    ...
    my_contentsinfo contentinfo;
    .....
    select into my_contentsInfo ...
    from table1, table 2
    where...

    return next my_contentsinfo;
    end;
    '

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Creating custom types is not an easy task, as you have to write a fair amount of c code to support the type.

    Try this instead.

    Create a empty, dummy table caled contentinfo that has the fields you wish to return from your function. (You can use Create Table ... As Select From... statement. and the drop any data that was inserted into the dummy table by the Select subquery.

    Then, ref this table in your function. Although you add a table, the overhead is fairly low, and it is MUCH easier than creating a user defined type.

    I try to give these dummy tables a unique prefix. dmy_contentinfo, for instance.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    May 2006
    Posts
    14
    Hi loguin,
    Thnx for your reply. I created a table dmy_contentinfo and my return type is now setof dmy_contentinfo, and m returnin next my_contents, where my_contents is of type dmy_contentinfo. To select the tuples i used select into my_contents ....
    This again selects a single tuple. create table table_name as column_names shuld work, but i don't know how exactly to implement it in my function. I am not sure what my return type should b since i'll b creating the table within the function, and also I am not sure how to return the table.. Can you plz provide an outline of hw exactly i shuld implement my function...thnx!

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You need to add a loop in your function... Ref the Loop...End Loop code below. Within the loop you issue return next record statements, when the loop is completed, you issue an 'empty' return.

    Code:
    CREATE OR REPLACE FUNCTION tds_schema.get_pos(startdate date, enddate date)
      RETURNS SETOF tds_schema.get_pos_type AS
    $BODY$
    DECLARE
    	rec  get_pos_type;
    
    BEGIN
    	for rec in 
    		SELECT	H.purchase_ord_date as "PO DATE", 
    			H.purchase_order_no as "PO", 
    			L.line_no as "LINE", 
    			L.item_no as "ITEM", 
    			L.quantity_order as "QTY", 
    			L.unit_of_measure as "UOM", 
    			L.description as "DESC" 
    		FROM purchase_order_header H, plineitem L 
    		Where (H.purchase_ord_date between startdate and enddate) and H.purchase_order_no = L.purchase_order_no 
    		ORDER BY H.purchase_ord_date, H.purchase_order_no, L.line_no
    	loop
    		return next rec;
    	end loop;
    	return;
    END;
    
    $BODY$
      LANGUAGE 'plpgsql' IMMUTABLE;
    Last edited by loquin; 05-17-06 at 12:43.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    May 2006
    Posts
    14
    Thanks!!

Posting Permissions

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