Execute PSQL procedure for multiple records matching criteria

Question

I need to run a procedure manually for a large set of records that meet a certain criteria. It works fine if I manually include an ID but for some reason it does not work if I just tell it to get a list of ids from a table. No errors; but the missing data doesn't get added to a table from the procedure.

As far as I understand it, this should work fine but it doesn't:

select fa_sptl_cross(id, geom) from focus_area where generation_id = 3;

But this works fine

select fa_sptl_cross(id, geom) from focus_area where id = 312231;

Even this should work, should it not?

select fa_sptl_cross(id, geom) from focus_area

Ideas?

AFAIK, it cannot be the procedure that is the issue because trying to do this fails with any procedure I try

Currently trying the selected answer from https://stackoverflow.com/questions/42920998/pl-pgsql-perform-vs-execute . Been running for 30 minutes so I'm hoping it's actually working — Vaesive, Nov 03 '21 at 19:10

score 0 · Answer 1 · answered Nov 06 '21 at 05:13

Please explain to me what does this function (fa_sptl_cross) does? If you can write the source code of this function that I could analyze this.

In general, I understand that when you are using where id = 312231 your query returns one record, that's why your function works fine. Because your function has an input parameter id. In other ways query return, many records, many id's, so and your function doesn't work. So I must view the source code of this function. I think so.

score 0 · Accepted Answer · answered Nov 08 '21 at 20:58

I got it to work using the information provided in PL/pgSQL perform vs execute

My solution is as follows:

DO $$
    BEGIN
      PERFORM habits4.fav_sptl_cross(focus_area_id, geom) from habits4.focus_area_version where generation_id = 3;
    END;
$$;

Execute PSQL procedure for multiple records matching criteria

2 Answers2