Syntax error near FOR LOOP in stored procedure Postgres

Question

Below is my stored procedure which I am trying to create. I am converting the table data into json and loop the JSON which I have created. But I am facing issue while doing so.

CREATE OR REPLACE FUNCTION file_compare()

    RETURNS text
    LANGUAGE 'plpgsql'

    COST 100
    VOLATILE 
    AS $BODY$
    declare 
    fpo_data jsonb;
    loopupto INTEGER := 0;

    begin

    select  json_agg(("fpdata"))::jsonb
    FROM (
      SELECT "fo_data" as fpdata
     from fpo
    limit 100
    ) t  INTO "fpo_data";

    FOR i IN "fpo_data"
  LOOP
    RAISE NOTICE 'output from space %', i;
  END LOOP;


    return fpo_data;
    end;
$BODY$;

I am getting the below error

 ERROR:  syntax error at or near ""fpo_data""
 LINE 27:  FOR i IN "fpo_data"

What is the issue? Please help!!

Please provide a data sample and the expected result, otherwise there is no way we can tell if your function makes sense. Btw, you're trying iterate over a json record. Is it supposed to be an array? — Jim Jones
– Jim Jones, Commented Jan 24, 2020 at 13:41
@JimJones That is a complex one . But I need to get all the data in "fo_data" in a json format and loop it — Sai sri
– Sai sri, Commented Jan 24, 2020 at 13:50
I hope that your code is not indented like it is in the question. — Luc M
– Luc M, Commented Jan 24, 2020 at 14:30

Jim Jones · Accepted Answer · 2020-01-27 11:32:02Z

1

You forgot to declare i, there are a few useless " and you're trying to iterate over a jsonb variable.

Assuming that fpo_data has a jsonb array stored, try this:

CREATE OR REPLACE FUNCTION file_compare()
RETURNS TEXT LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$
DECLARE
  fpo_data jsonb;
  i JSONB;
BEGIN
  SELECT json_agg((fpdata))::jsonb
  FROM (SELECT fo_data AS fpdata
        FROM fpo LIMIT 100
    ) t  INTO fpo_data; 
  FOR i IN SELECT * FROM jsonb_array_elements(fpo_data) LOOP
    RAISE NOTICE 'output from space %', i;
  END LOOP;
  RETURN fpo_data;
END;
$BODY$;

Edit: To retrieve only the element bene_first_name in the raise notice statement just do:

RAISE NOTICE 'output from space %', (i->>0)::JSONB->'bene_first_name';

edited Jan 27, 2020 at 11:32

answered Jan 24, 2020 at 13:36

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sai sri Over a year ago

I am getting fpo_data as below in return. If i try to print 'bene_firstname' like this RAISE NOTICE 'output from space %', i->>'bene_firstname' I am getting the error as ERROR: operator does not exist: record ->> unknown. How to I get 'bene_firstname' from the below json array ["{\"bene_first_name\":{\"value\":\"Chris\"},\"bene_last_name\":{\"value\":\"Ronald\"}}", "{\"bene_first_name\":{\"value\":\"John\"},\"bene_last_name\":{\"value\":\"Wick\"}}", "{\"bene_first_name\":{\"value\":\"James\"},\"bene_last_name\":{\"value\":\"Cooper\"}}"]

Sai sri Over a year ago

My postgres version is 10.11

Collectives™ on Stack Overflow

Syntax error near FOR LOOP in stored procedure Postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related