postgresql cannot open INSERT query as cursor

Question

I'm trying to generate dynamic query to insert results of dynamic select into table. my code is the following:

CREATE OR REPLACE FUNCTION public.report_get_result(
datekey integer)
RETURNS setof public.logic_result_rcd
LANGUAGE 'plpgsql'
COST 100
VOLATILE 
AS $BODY$

DECLARE
     LogicID text;
     SheetName text;
     Row_ID text;
     Column_ID text;
     FromTable text;
     Operation text;
     Amount text;
     CriteriaType_1 text;
     Function_1 text;
     Criteria_1 text;
     CriteriaType_2 text;
     Function_2 text;
     Criteria_2 text;
     CriteriaType_3 text;
     Function_3 text;
     Criteria_3 text;
     sql text;
     INC Integer;

begin
 DROP TABLE IF EXISTS loans;
 create temp table loans as
 select * from loan.vfact_state_principal where "DateKey" = datekey;
 DECLARE cursor_logic REFCURSOR;
 BEGIN
 OPEN cursor_logic for SELECT "LogicID" FROM logic_table_rcd;
 LOOP
    FETCH cursor_logic INTO INC;
    if not found then exit;
    end if;
    BEGIN

    select into LogicID "LogicID" from public.logic_table_rcd WHERE 
"LogicID" = 1;
     select into SheetName "SheetName" from public.logic_table_rcd WHERE 
"LogicID" = 1;
     select into Row_ID "Row_ID" from public.logic_table_rcd WHERE "LogicID" 
= 1;
     select into Column_ID "Column_ID" from public.logic_table_rcd WHERE 
"LogicID" = 1;
     select into FromTable "FromTable" from public.logic_table_rcd WHERE 
"LogicID" = 1;
     select into Operation "Operation" from public.logic_table_rcd WHERE 
"LogicID" = 1;
     select into Amount "Amount" from public.logic_table_rcd WHERE "LogicID" 
= 1;
     select into CriteriaType_1 CASE WHEN "CriteriaType_1" <> '' OR 
"CriteriaType_1" is not null THEN (' WHERE "' || "CriteriaType_1" || '"') 
ELSE '' END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into Function_1 CASE WHEN "Function_1" is null THEN '' ELSE 
"Function_1" END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into Criteria_1 CASE WHEN "Criteria_1" is null THEN '' ELSE 
"Criteria_1" END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into CriteriaType_2 CASE WHEN "CriteriaType_2" <> '' OR 
"CriteriaType_2" is not null THEN ' AND "' || "CriteriaType_2" || '"' ELSE 
'' END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into Function_2 CASE WHEN "Function_2" is null THEN '' ELSE 
"Function_2" END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into Criteria_2 CASE WHEN "Criteria_2" is null THEN '' ELSE 
"Criteria_2" END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into CriteriaType_3 CASE WHEN "CriteriaType_3" <> '' or 
"CriteriaType_3" is not null THEN ' AND "' || "CriteriaType_3" || '"' ELSE 
'' END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into Function_3 CASE WHEN "Function_3" is null THEN '' ELSE 
"Function_3" END from public.logic_table_rcd WHERE "LogicID" = 1;
     select into Criteria_3 CASE WHEN "Criteria_3" is null THEN '' ELSE 
"Criteria_3" END from public.logic_table_rcd WHERE "LogicID" = 1;
     
 sql:= 'INSERT INTO public.logic_result_rc SELECT ' || INC::text || ', 1, ' 
|| DateKey::text || ', ''' || 'RCD' || ''', ''' || SheetName::text || ''', ' 
|| Row_ID::text || ', ' 
|| Column_ID::text || ', ' || Operation || '("' || Amount || '")' || ' FROM 
' || FromTable
    || CriteriaType_1 || ' ' || Function_1 || ' ' || Criteria_1
    || CriteriaType_2 || ' ' || Function_2 || ' ' || Criteria_2
    || CriteriaType_3 || ' ' || Function_3 || ' ' || Criteria_3;

RETURN QUERY EXECUTE sql;
END;
END LOOP;
CLOSE cursor_logic;
END;
END;
$BODY$;
ALTER FUNCTION public.report_get_result(integer)
OWNER TO postgres;

But after execution I get the next error:

 cannot open INSERT query as cursor

All variables are assigned correctly. may be insert must be somewhere else outside cursor? does INSERT INTO .... FETCH ALL statement exist?

Formatting changes for readability.

Chris Travers
– Chris Travers

2017-09-26 15:45:40 +00:00
Commented Sep 26, 2017 at 15:45 — Chris Travers
– Chris Travers, Commented Sep 26, 2017 at 15:45

Łukasz Kamiński · Accepted Answer · 2017-09-26 11:41:45Z

5

INSERT by default doesn't return any rows, so there is nothing to fetch. You can fix this by appending your sql string with RETURNING *, with should return contents inserted into public.logic_result_rc.

So it would like this: RETURN QUERY EXECUTE concat(sql, ' RETURNING *');

Basic syntax is:

INSERT INTO table_name ( column_name [, ...] )
    VALUES ( ) | query
    RETURNING * --or list of columns, same syntax like for SELECT

answered Sep 26, 2017 at 11:41

Łukasz Kamiński

6,0401 gold badge26 silver badges33 bronze badges

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

Collectives™ on Stack Overflow

postgresql cannot open INSERT query as cursor

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related