Returning SETOF in plpgsql

[twinklyblue]

Hi thescripts team,

I would like to ask for your help once again regarding returning set of records in plpgsql. I followed examples presented in other sites regarding this function but my script doesnt work.

here's my code

create or replace function find_postal(varchar, varchar, varchar) returns setof record as
'
    declare 
        ret_row record;
        prefnme alias for $1;
        cty alias for $2;
        dname alias for $3;
    begin    
    for ret_row in select * from area where pref_nme like coalesce ($1,''%'') or city like COALESCE($2,''%'') or district_nme like coalesce ($3,''%'') loop
        return next ret_row;
    end loop;
    return;
    end; 
'
language 'plpgsql';

And here's the error being returned:

ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function "find_postal" line 8 at return next

Thanks in advance for those who can help me

[michaelb]

I think this is the easiest way to make this function work:

1. Drop the function
2. Change the function return type from "setof record" to "setof <table-name>"
   ("setof area" in this case)
3. Optionally, you may also like to change declaration "ret_row record;" to "ret_row area%rowtype;"
   (it should work either way)
4. Recompile, and don't forget that functions returning setof must be called as
   select * from func_name (...)
   select func_name (...) will not work.

[twinklyblue]

Hi michaelb,

Thanks again for helping! It worked now!

[AhmetYOL]

hi
i have the problem like this but i couldn't solve this yet.
i have the function below;

CREATE OR REPLACE FUNCTION "public"."fnc_dupps" (x integer, out y integer, out z integer, out t varchar) RETURNS SETOF "pg_catalog"."record" AS
$body$
declare cur_koyler cursor for
  select koy_bolgeid,koy_idno from tbl_koyler;
declare k integer;
begin
 open cur_koyler;
 loop
   exit when not found;
   fetch cur_koyler into y,z;
   raise exception 'Hata ';
   return next;
 end loop;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 

When i execute the function it turns me nothing.
but

select koy_bolgeid,koy_idno from tbl_koyler
 

query has a records. i send this for example. the original has the much outputs. so i dont want to set return type to table. (if there is no way i have to do sure.)
Best Regards.
Pg : 8.2
Platform : xp sp2
Best Regards.

[AhmetYOL]

hi
i create the type for this function

create type typ_dupps as
(x integer,
y integer,
z varchar )
 

and the functions latest version is below

CREATE OR REPLACE FUNCTION "public"."fnc_dupps" (x integer) RETURNS SETOF "pg_catalog"."record" AS
$body$
declare sonuc typ_dupps;
declare crs_koyler cursor for
select koy_idno,koy_bolgeid,koy_ad from tbl_koyler;
declare i integer;
declare k varchar;
 
begin
open crs_koyler;
loop
  exit when not found;
  fetch crs_koyler into sonuc.x,sonuc.y;
  return next sonuc;
end loop;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
 

it is running perfectly .
Best Regards.

[CHETANRAJAN]

HI
i tried to run the following function. Can you help me. It is giving runtime error as

ERROR: cursor "cusr" does not exist
CONTEXT: PL/pgSQL function "com_attr_ins" line 15 at fetch

The function code is

CREATE OR REPLACE FUNCTION "public"."com_attr_ins" () RETURNS "pg_catalog"."void" AS
$body$
DECLARE
       attr_id      integer;
       cat_attr_id  integer;
       nam          varchar(512);
       lbl          varchar(512);
       val_type     bigint;
       aggr_type    bigint;
       allow_null   bool;
       cusr         CURSOR for select id , comp_cat_attr_id from component_attr;
BEGIN
     loop
         fetch cusr INTO attr_id , cat_attr_id;
         if not found then
         exit;
         end if;
         select into nam name from component_category_attr where id = cat_attr_id;
         select into lbl label from component_category_attr where id = cat_attr_id;
         select into val_type value_type from component_category_attr where id = cat_attr_id;
         select into aggr_type AGGREGATION_TYPE from component_category_attr where id = cat_attr_id;
         select into allow_null ALLOW_NULL_VALUE from component_category_attr where id = cat_attr_id;
          update temp_attr set label = lbl ,
                          value_type = val_type ,
                          aggregation_type = aggr_type,
                          allow_null_value = allow_null
                      where id = attr_id and COMP_CAT_ATTR_ID = cat_attr_id;
     end loop;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

[rski]

see here

i don't see where do you open a cursor