By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,984 Members | 2,832 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,984 IT Pros & Developers. It's quick & easy.

Returning SETOF in plpgsql

P: 37
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
Expand|Select|Wrap|Line Numbers
  1. create or replace function find_postal(varchar, varchar, varchar) returns setof record as
  2. '
  3.     declare 
  4.         ret_row record;
  5.         prefnme alias for $1;
  6.         cty alias for $2;
  7.         dname alias for $3;
  8.     begin    
  9.     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
  10.         return next ret_row;
  11.     end loop;
  12.     return;
  13.     end; 
  14. '
  15. 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
Jul 27 '07 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 534
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.
Jul 29 '07 #2

P: 37
Hi michaelb,

Thanks again for helping! It worked now!
Jul 30 '07 #3

P: 7
hi
i have the problem like this but i couldn't solve this yet.
i have the function below;
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION "public"."fnc_dupps" (x integer, out y integer, out z integer, out t varchar) RETURNS SETOF "pg_catalog"."record" AS
  2. $body$
  3. declare cur_koyler cursor for
  4.   select koy_bolgeid,koy_idno from tbl_koyler;
  5. declare k integer;
  6. begin
  7.  open cur_koyler;
  8.  loop
  9.    exit when not found;
  10.    fetch cur_koyler into y,z;
  11.    raise exception 'Hata ';
  12.    return next;
  13.  end loop;
  14. end;
  15. $body$
  16. LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
  17.  
When i execute the function it turns me nothing.
but
Expand|Select|Wrap|Line Numbers
  1. select koy_bolgeid,koy_idno from tbl_koyler
  2.  
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.
Aug 8 '07 #4

P: 7
hi
i create the type for this function
Expand|Select|Wrap|Line Numbers
  1. create type typ_dupps as
  2. (x integer,
  3. y integer,
  4. z varchar )
  5.  
and the functions latest version is below
Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION "public"."fnc_dupps" (x integer) RETURNS SETOF "pg_catalog"."record" AS
  2. $body$
  3. declare sonuc typ_dupps;
  4. declare crs_koyler cursor for
  5. select koy_idno,koy_bolgeid,koy_ad from tbl_koyler;
  6. declare i integer;
  7. declare k varchar;
  8.  
  9. begin
  10. open crs_koyler;
  11. loop
  12.   exit when not found;
  13.   fetch crs_koyler into sonuc.x,sonuc.y;
  14.   return next sonuc;
  15. end loop;
  16. end;
  17. $body$
  18. LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
  19.  
it is running perfectly .
Best Regards.
Aug 8 '07 #5

P: 1
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

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE FUNCTION "public"."com_attr_ins" () RETURNS "pg_catalog"."void" AS
  2. $body$
  3. DECLARE
  4.        attr_id      integer;
  5.        cat_attr_id  integer;
  6.        nam          varchar(512);
  7.        lbl          varchar(512);
  8.        val_type     bigint;
  9.        aggr_type    bigint;
  10.        allow_null   bool;
  11.        cusr         CURSOR for select id , comp_cat_attr_id from component_attr;
  12. BEGIN
  13.      loop
  14.          fetch cusr INTO attr_id , cat_attr_id;
  15.          if not found then
  16.          exit;
  17.          end if;
  18.          select into nam name from component_category_attr where id = cat_attr_id;
  19.          select into lbl label from component_category_attr where id = cat_attr_id;
  20.          select into val_type value_type from component_category_attr where id = cat_attr_id;
  21.          select into aggr_type AGGREGATION_TYPE from component_category_attr where id = cat_attr_id;
  22.          select into allow_null ALLOW_NULL_VALUE from component_category_attr where id = cat_attr_id;
  23.           update temp_attr set label = lbl ,
  24.                           value_type = val_type ,
  25.                           aggregation_type = aggr_type,
  26.                           allow_null_value = allow_null
  27.                       where id = attr_id and COMP_CAT_ATTR_ID = cat_attr_id;
  28.      end loop;
  29. end;
  30. $body$
  31. LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
Feb 21 '08 #6

Expert 100+
P: 700
see here

i don't see where do you open a cursor
Feb 21 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.