How can I query a custom datatype object inside an array of said custom datatype in PL/pgSQL?

Question

Suppose I have: CREATE TYPE compfoo AS (f1 int, f2 text); And I create a table foo containing two columns: fooid and fooname, corresponding to the fields of compfoo, later I insert some records 1, aa, 2, bb, 3, cc

Then, I define a PL/pgSQL function (more or less as follows:)

create or replace function foo_query()
returns text
language plpgsql
as $$
    declare
      r compfoo;
      arr compfoo [];
      footemp compfoo;
      result text;
    begin
      for r in
        select * from foo where fooid = 1 OR fooid = 2
        loop
        arr := array_append(arr, r);
      end loop;
      foreach footemp in array arr
        loop
        select footemp.f1 into result where footemp.f1 = 1;
      end loop;
    return result;
    end;
$$

Where I query first foo by column names and save the results into arr, an array of compfoo. Later, I iterate over arr and try to query the elements by their fieldnames as defined in compfoo.

I don't get an error per se in Postgres but the result of my function is null.

What am I doing wrong?

Pavel Stehule · Accepted Answer · 2022-10-28 04:07:28Z

The RAISE NOTICE should be your best friend. You can print the result of some variables at some points of your code. The basic issue are not well initialized values. The arr variable is initialized by NULL value, and any operation over NULL is NULL again.

Another problem is in select footemp.f1 into result where footemp.f1 = 1; statement. SELECT INTO in Postgres overwrite the target variable by NULL value when an result is empty. In second iteration, the result of this query is empty set, and the result variable is set on NULL.

The most big problem of your example is style of programming. You use ISAM style, and your code can be terrible slow.

Don't use array_append in cycle, when you can use array_agg function in query, and you don't need cycle,
Don't use SELECT INTO when you don't read data from tables,
Don't try to repeat Oracle' pattern BULK COLLECT and FOREACH read over collection. PostgreSQL is not Oracle, uses very different architecture, and this pattern doesn't increase performance (like on Oracle), but probably you will lost some performance.

Your fixed code can looks like:

CREATE OR REPLACE FUNCTION public.foo_query()
 RETURNS text
 LANGUAGE plpgsql
AS $function$
declare
  r compfoo;
  arr compfoo [] default '{}'; --<<<
  footemp compfoo;
  result text;
begin
  for r in
     select * from foo where fooid = 1 or fooid = 2
  loop
    arr := array_append(arr, r);
  end loop;
  
  foreach footemp in array arr
  loop
    if footemp.f1 = 1 then --<<<
      result := footemp.f1;
    end if;
  end loop;
  
  return result;
end;
$function$
postgres-# ;

It returns expected result. But it is perfect example how don't write stored procedures. Don't try to replace SQL in stored procedures. All code of this procedure can be replaced just by one query. In the end this code can be very slow on bigger data.

Collectives™ on Stack Overflow

How can I query a custom datatype object inside an array of said custom datatype in PL/pgSQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related