CODEWITHSUNDEEP
postgresqlplpgsql
Neel Basu
Neel Basu

Reputation: 12904

PL/PgSQL calling a function inside a loop giving error

The code bellow is giving error on w_add_ax_extra(1, 'k', 'v') previously it was w_add_ax_extra(some_id, kv.k, kv.v) I changed it to k, v to reproduce the same error

declare
  kv record;
begin
  -- Lines skipped
  for kv in select * from (select (each(extras)).*) as f(k,v) loop
    raise notice 'key=%,value=%',kv.k,kv.v;
    w_add_ax_extra(1, 'k', 'v');
  end loop;
  -- Lines Skipped
end

I am getting Syntax Error but could not understand what I am missing

ERROR:  syntax error at or near "w_add_ax_extra"
LINE 1: w_add_ax_extra(1, 'k', 'v')

However If I do dummy = w_add_ax_extra(1, 'k', 'v') it works. Yes this function returns an integer. But I don't need to store it here. Is it mandatory to hold the return value ?

Upvotes: 1

Views: 4724

Answers (1)

mu is too short
mu is too short

Reputation: 434585

From the fine manual:

39.5.2. Executing a Command With No Result

[...]

Sometimes it is useful to evaluate an expression or SELECT query but discard the result, for example when calling a function that has side-effects but no useful result value. To do this in PL/pgSQL, use the PERFORM statement:

PERFORM query;

Emphasis mine. You're not calling the function by saying something like f();, you need to perform f(); or select f() into ...;:

for kv in select * from (select (each(extras)).*) as f(k,v) loop
    raise notice 'key=%,value=%',kv.k,kv.v;
    perform w_add_ax_extra(1, 'k', 'v');
end loop;

Upvotes: 7

Related Questions