CODEWITHSUNDEEP
sqlpostgresqlplpgsql
Sai sri
Sai sri

Reputation: 545

Syntax error near FOR LOOP in stored procedure Postgres

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!!

Upvotes: 1

Views: 720

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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';

Upvotes: 1

Related Questions