I am creating a stored procedure by passing a type and do a loop inside the procedure so that I can insert each info type
This is my type
create type leketo as(id integer, name text);
Function that inserts rows into my type
CREATE OR REPLACE FUNCTION getLeketo()
RETURNS SETOF leketo AS
$BODY$
declare
l leketo;
begin
l.id := 1;
l.name := 'One';
return next l;
l.id := 2;
l.name := 'Two';
return next l;
l.id := 3;
l.name := 'Three';
return next l;
l.id := 4;
l.name := 'Four';
return next l;
l.id := 5;
l.name := 'Five';
return next l;
end
$BODY$
LANGUAGE plpgsql VOLATILE;
Running the function returns this to me
select * from getLeketo()
1 One
2 Two
3 Three
4 Four
5 Five
In this procedure we will go through all the rows
CREATE OR REPLACE FUNCTION loopLeketo(pl leketo)
RETURNS void AS
$BODY$
declare
l leketo;
begin
for l in (select * from pl) loop
raise notice '----------------------------------------';
raise notice 'id=%, name=%', l.id, l.name;
end loop;
end
$BODY$
LANGUAGE plpgsql VOLATILE;
If I try this I get the following message
DO $$
declare
l leketo;
begin
select * from getLeketo() into l;
PERFORM loopLeketo(l);
end$$;
ERROR: relation "pl" does not exist
You get that error message because a parameter (in your case pl) cannot occur in the FROM clause of a query, so PostgreSQL will interpret pl as a table name.
The deeper problem is that you try to assign a set of values to a single variable, which will not work. Your SELECT ... INTO statement will only store the first result in the variable l.
You don't tell us what you really want to achieve, but I can think of two approaches for the problem you show:
Handle the query results one by one. PL/pgSQL code would look like that:
DEFINE
l leketo;
BEGIN
FOR l IN SELECT * FROM getleketo() LOOP
RAISE NOTICE 'id=%, name=%', l.id, l.name;
END LOOP;
END;
Define getleketo() not as RETURNS SETOF leketo, but as RETURNS refcursor and have it return a cursor for the results. Then you can assign the whole query result to a variable of type refcursor and use this as an argument to the loopleketo function.
See the documentation for details.