Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2006
    Posts
    16

    Exclamation Unanswered: Execute not returning rows

    Hi,

    I have a function which returns a table type. But when i execute the same, it returns 0 rows. I tried inserting the query in a temp table and executed from outside, that worked fine, Any comments. I am pasting the function below -

    Code:
    -- Type: bus_adv_set
    --drop table bus_adv_set cascade;
    
    /*
    CREATE TABLE bus_adv_set 
       (bp_id bigint,
        bp_name character varying(1000),
        bp_desc character varying(1000),
        bp_uuid character varying(25),
        bp_employees bigint,
        bp_revenues double precision,
        bp_rating double precision,
        bp_zubican_ranking bigint,
        bp_num_connections bigint,
        bp_yr_fnd timestamp without time zone,
        add_city character varying(100),
        add_state character varying(100),
        add_zip_code character varying(100),
        latitude bigint,
        longitude bigint);
    ALTER TABLE bus_adv_set OWNER TO postgres;
    */
    
    CREATE OR REPLACE FUNCTION fn_business(p_company_name character varying, p_keywords character varying, p_num_emp_min bigint, p_num_emp_max bigint, p_rev_min bigint, p_rev_max bigint, p_lat_min bigint, p_lat_max bigint, p_long_min bigint, p_long_max bigint, p_years bigint, p_zub_rank_min bigint, p_zub_rating_min double precision, p_ic_code character varying)
      RETURNS SETOF bus_adv_set AS
    $BODY$
    declare
    cursor_bus refcursor;
    return_set bus_adv_set%rowtype;
    v_ic_code varchar(100);
    v_query varchar(4000);
    v_select varchar(4000);
    v_from varchar(4000);
    v_where varchar(4000);
    v_bp_id bigint;
    v_bp_name varchar(100);
    v_bp_desc varchar(200);
    v_bp_uuid varchar(25);
    v_bp_employees bigint;
    v_bp_revenues float;
    v_bp_bp_rating float;
    v_bp_zubican_ranking bigint;
    v_bp_num_connections bigint;
    v_bp_yr_fnd timestamp;
    v_add_city varchar(10);
    v_add_state  varchar(10);
    v_add_zip_code varchar(10);
    v_latitude bigint;
    v_longitude bigint;
    v_ic_code_string varchar(4000);
    
    BEGIN
    
    v_select := 'select bp.bp_id, bp.bp_name, bp.bp_desc, bp.bp_uuid, bp.bp_employees, bp.bp_revenues, bp.bp_rating, bp.bp_zubican_ranking, bp.bp_num_connections,  bp.bp_yr_fnd,';
    v_select := v_select || 'add.add_city, add.add_state, add.add_zip_code, add.latitude, add.longitude ';
    v_from :=  ' from business_profile bp, address add, business_address ba ';
    v_where := ' where bp.bp_id = ba.bp_id ';
    v_where := v_where || 'and ba.address_id = add.address_id ';
    
    IF p_company_name is NOT NULL THEN
    v_where := v_where || ' limit 100 '; -- and bp.bp_desc_fti @@ to_tsquery('''||p_company_name||''') ';
    
    --v_where := v_where || 'and bp.bp_name_trade_name_fti @@ to_tsquery('''||$1||''') '; -- and bp.bp_desc_fti @@ to_tsquery('''||p_company_name||''') ';
    --v_where := v_where || 'and bp.bp_desc_fti @@ to_tsquery('''||p_company_name||''') ';
    END IF;
    
    IF p_num_emp_min != 0 THEN
    v_where := v_where || 'and bp.bp_employees > ' ||p_num_emp_min  ;
    END IF;
    ----------------------------
    IF p_num_emp_max != 0 THEN
    v_where := v_where || 'and bp.bp_employees <= '||p_num_emp_max ;
    END IF;
    
    IF p_rev_min != 0 THEN
    v_where := v_where || 'and bp.bp_revenues >= '||p_rev_min  ;
    END IF;
    
    IF p_rev_max != 0 THEN
    v_where := v_where || 'and bp.bp_revenues <= '||p_rev_max ;
    END IF;
    
    IF p_lat_min != 0 and p_lat_max != 0 THEN
    v_where := v_where || 'and add.latitude between  '||p_lat_min||' and '||p_lat_max;
    END IF;
    
    IF p_long_min != 0 and p_long_max != 0 THEN
    v_where := v_where || 'and add.longitude between  '||p_long_min||' and '||p_long_max ;
    END IF;
    
    IF p_years IS NOT NULL THEN
    v_where := v_where || 'and cast(substr(age(localtimestamp,bp.bp_yr_fnd),1,1) as int) >= '||p_years ;
    END IF;
    
    IF p_zub_rating_min != 0 THEN
    v_where := v_where || 'and bp.bp_rating >= '||p_zub_rating_min ;
    END IF;
    
    IF p_zub_rank_min != 0 THEN
    v_where := v_where || 'and bp.bp_zubican_ranking >= '||p_zub_rank_min ;
    END IF;
    
    IF p_ic_code is not NULL then
    	v_ic_code_string := p_ic_code;
    	v_from := v_from || ', industry_class ic, business_industry_class bic ';
    	v_where := v_where || ' and ic.ic_id = bic.ic_id and bp.bp_id = bic.bp_id and (';
    	while position( ',' IN v_ic_code_string) > 0 loop
    		select substr (v_ic_code_string,1, position( ',' IN v_ic_code_string)-1) into v_ic_code;
    		v_where := v_where || '  ic.ic_code like '''||v_ic_code ||''' OR ';
    		v_ic_code_string = substr(v_ic_code_string,position( ',' IN v_ic_code_string)+1, length(v_ic_code_string));
    	end loop;
    	v_where := v_where || '  ic.ic_code like '''||v_ic_code_string ||''')';
    END IF;
    
    ----------------------------------------------
    
    v_query := v_select || v_from || v_where;
    v_query := v_query || ';';
    
    delete from test1;
    insert into test1 values (v_query);
    
    EXECUTE  v_query;
    
    
    /*loop
    FETCH cursor_bus into return_set;
    IF NOT FOUND THEN
      EXIT;
    END IF;
    return next return_set;
    end loop;
    close cursor_bus;
    */
    --EXECUTE v_query into return_set;
    
    --EXECUTE v_query into v_bp_id,v_bp_name ,v_bp_desc ,v_bp_employees ,vp_bp_rating ,v_bp_zubican_ranking ,v_bp_num_connections ,
    --v_add_city ,v_add_state  ,v_add_zip_code ,v_latitude ,v_longitude ;
    
    END
    $BODY$
      LANGUAGE 'plpgsql' VOLATILE;
    ALTER FUNCTION fn_business(p_company_name character varying, p_keywords character varying, p_num_emp_min bigint, p_num_emp_max bigint, p_rev_min bigint, p_rev_max bigint, p_lat_min bigint, p_lat_max bigint, p_long_min bigint, p_long_max bigint, p_years bigint, p_zub_rank_min bigint, p_zub_rating_min double precision, p_ic_code character varying) OWNER TO postgres;
    Last edited by loquin; 04-14-08 at 18:33. Reason: add [code] [/code] tags for readability.

Posting Permissions

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