473,883 Members | 2,150 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Returning SETOF in plpgsql

37 New Member
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_posta l" line 8 at return next


Thanks in advance for those who can help me
Jul 27 '07 #1
6 18386
michaelb
534 Recognized Expert Contributor
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
twinklyblue
37 New Member
Hi michaelb,

Thanks again for helping! It worked now!
Jul 30 '07 #3
AhmetYOL
7 New Member
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
AhmetYOL
7 New Member
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
CHETANRAJAN
1 New Member
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_i ns" 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
rski
700 Recognized Expert Contributor
see here

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

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

Similar topics

1
8219
by: Julie May | last post by:
I have 90% of my function working and I know to get the next 10% it is justa matter of getting the quotations and the escaping quotations correct. Here is the portion that does work: <working code> -- Function: public.get_factory_ytd() CREATE or replace FUNCTION public.get_factory_ytd() RETURNS setof record AS' declare ytd_record record; d_date record; begin for d_date in select distinct delivery_date from load_info_table order...
22
3734
by: Christopher Murtagh | last post by:
Greetings, I'm trying to write a pl/perl function that will return multiple rows. I've looked all over the web and only found vague references as to how to do this (some said it was possible, and some said it wasn't but it was for older versions of Postgres). Basically I would *love* to be able to do something like this: SELECT some_id
2
3530
by: Christopher Murtagh | last post by:
Greetings, I've got a fairly simple function that I'm trying to return a set, but seem to have come across a stumbling block. When I execute the function below (the input params are not used at the moment) I get this: chris=# select htdig('foo', 'foo'); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "htdig" line 14 at return next
9
19513
by: Ron St-Pierre | last post by:
On a daily basis I place a lot of data into the empty table dailyList, and from that data update certain fields in currentList. I thought that using a function would be a good way to do this(?). However I get the following error when I run updateCurrentData(): ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "updatecurrentcata" line 6 at return next I've googled and tried variations on the...
3
5336
by: ezra epstein | last post by:
I'm been banging my head over this for a little while now. Here's a simple function to return a record: <code> CREATE OR REPLACE FUNCTION create_item_record_for_override(INTEGER, VARCHAR ) RETURNS RECORD AS ' DECLARE rec RECORD;
9
10942
by: Karl O. Pinc | last post by:
I want to return multiple values, but not a set, only a single row, from a plpgsql function and I can't seem to get it to work. (I suppose I'd be happy to return a set, but I can't seem to make that work either. Anyway, what's wrong with this?) Version is: $ rpm -q postgresql
5
14402
by: Robert Fitzpatrick | last post by:
Can someone point me to some more information or perhaps show an example of returning a recordset from a plpgsql function. I'd like to send an argument or arguments to the function, do some queries to return a set of records. I've done several functions that return one value of one type, but nothing that returns a set. -- Robert
2
12483
by: Vladimir M | last post by:
Hi I am writing a function with PL/pgSQL, which returns result of some complex query with several tables. In manual i found such example: CREATE OR REPLACE FUNCTION Test() RETURNS SETOF table1 AS $$ DECLARE ret_row RECORD; BEGIN FOR ret_row IN SELECT * FROM table1 LOOP RETURN NEXT ret_row;
0
3607
by: lazybee26 | last post by:
Hello – I’m trying to findout a better solution to this approach. Currently if I have to return columns from multiple tables, I have to define my own TYPE and then return SETOF that type in the function. I’ve provided an example below. Now, if I have to add a column to the select query, I have drop the existing TYPE definition, create a new TYPE with the new column added to it, and then modify the function sql to return this...
0
9945
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9798
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10766
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10863
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10422
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9588
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5807
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6007
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
3241
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.