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?
CodePudding user response:
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 usearray_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.