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_posta l" line 8 at return next
Thanks in advance for those who can help me
6 18371 michaelb 534
Recognized Expert Contributor
I think this is the easiest way to make this function work: - Drop the function
- Change the function return type from "setof record" to "setof <table-name>"
("setof area" in this case) - Optionally, you may also like to change declaration "ret_row record;" to "ret_row area%rowtype;"
(it should work either way) - Recompile, and don't forget that functions returning setof must be called as
select * from func_name (...)
select func_name (...) will not work.
Hi michaelb,
Thanks again for helping! It worked now!
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.
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.
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 - 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 700
Recognized Expert Contributor
see here
i don't see where do you open a cursor
Sign in to post your reply or Sign up for a free account.
Similar topics |
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...
|
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
|
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
|
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...
|
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;
| |
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
|
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
|
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;
|
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...
|
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...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
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...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
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...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |