-2

I want to write a function like below.Whats wrong in it?

CREATE OR REPLACE FUNCTION myFunc(abc BIGINT, sss myTable.Col1%Type) AS 
DECLARE 
myRec RECORD;
tab_Rec RECORD[]; --This i want to as a table (i dont know the column details though). 
--Declaring it as array fails

BEGIN
SELECT INTO myREC FROM public.myOtherFunc(abc, sss);


--Returns a table i.e. multiple rows and columns
SELECT * INTO tab_Rec FROM public.myAddFunc(abc);


--I want to do this now
for x in 1 .. array_length(tab_Rec, 1)
loop
         ---some logic
end loop;


END;
Sushant
  • 3
  • 4
  • Doesn't the server tell you what is wrong with it with an error message? Why make us guess what error message you received? – jjanes Sep 11 '20 at 14:59
  • As you could see, line 5 has a comment that says declaring an array of RECORD fails. And i am not able to use SET OF RECORD as well. So my question is how can i get a table like result set in a variable and then work on it to apply some logic later in same function. – Sushant Sep 13 '20 at 12:03

1 Answers1

0

There are no "table variables" in PL/pgSQL. You might use a temporary table or a cursor for the purpose.

Better yet, to loop through a result set, use the implicit cursor of a FOR loop. See:

Minimal example:

CREATE OR REPLACE FUNCTION my_func(abc bigint, sss mytable.col1%TYPE)
  RETURNS void  -- or whatever
  LANGUAGE plpgsql AS 
$func$
DECLARE
   _rec record;
BEGIN
   FOR _rec IN
      SELECT * FROM public.myaddfunc(abc)
   LOOP
      -- SOME LOGIC;
   END LOOP;
END
$func$;

You may not even need the loop, if "SOME LOGIC" can be integrated in a plain query ...

Erwin Brandstetter
  • 175,982
  • 27
  • 439
  • 600
  • For now this seems to be the solution for my problem. I have marked it as answer. @Erwin-brandstetter, in addition to this "SOME LOGIC" would actually be returning a result set (not inside the loop) but the column names and the type wont be static. So is it possible to return such result set from a function – Sushant Sep 14 '20 at 09:52
  • @Sushant: Basically, no. SQL is a statically typed language, functions need to define a static return type. But there are various ways around this, depending on the exact situation and requirements. I suggest you ask a new (clearly defined!) question. – Erwin Brandstetter Sep 14 '20 at 12:15
  • Thanks @Erwin. I have created a new question with more details here... https://dba.stackexchange.com/questions/275495/how-to-get-table-like-dynamic-structure-output-returned-from-postgresql-function – Sushant Sep 14 '20 at 13:19