postgresql insert dynamic query result into table in cursor

Question

i have the following function, that generates dynamic query and at the end i want to insert result of dynamic query into table, but the error i get is `

ERROR:  query has no destination for result data

HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function report_get_result(integer) line 46 at SQL statement
SQL statement "SELECT report_get_result(20150131)"
PL/pgSQL function inline_code_block line 2 at PERFORM
********** Error **********'

body of the function is:

CREATE OR REPLACE FUNCTION report_get_result (
datekey integer
) returns setof logic_result_rcd
AS
$body$
DECLARE
    LogicID integer;
    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 execute ('SELECT "LogicID" FROM logic_table_rcd');
 LOOP
    FETCH cursor_logic INTO INC;
    if not found then exit;
    end if;
    BEGIN
 SELECT  LogicID = "LogicID"
            ,SheetName = "SheetName"
            ,Row_ID = "Row_ID"::text
            ,Column_ID = "Column_ID"::text
            ,FromTable = "FromTable"
            ,Operation = "Operation"
            ,Amount = "Amount"
            ,CriteriaType_1 = CASE WHEN "CriteriaType_1" <> '' THEN ('WHERE 
' || "CriteriaType_1") ELSE '' END
            ,Function_1 = CASE WHEN "Function_1" is null THEN 'Empty' ELSE 
"Function_1" END
            ,Criteria_1 = CASE WHEN "Criteria_1" is null THEN 'Empty' ELSE 
"Criteria_1" END
            ,CriteriaType_2 = CASE WHEN "CriteriaType_2" <> '' THEN ' AND ' 
|| "CriteriaType_2" ELSE '' END
            ,Function_2 = CASE WHEN "Function_2" is null THEN 'Empty' ELSE 
"Function_2" END
            ,Criteria_2 = CASE WHEN "Criteria_2" is null THEN 'Empty' ELSE 
"Criteria_2" END
            ,CriteriaType_3 = CASE WHEN "CriteriaType_3" <> '' THEN ' AND ' 
|| "CriteriaType_3" ELSE '' END
            ,Function_3 = CASE WHEN "Function_3" is null THEN 'Empty' ELSE 
"Function_3" END
            ,Criteria_3 = CASE WHEN "Criteria_3" is null THEN 'Empty' ELSE 
"Criteria_3" END
     FROM public.logic_table_rcd WHERE "LogicID" = INC;
     
sql:= 'INSERT INTO public.logic_result_rcd SELECT ' || INC::text || ', 1, ' 
|| EntityID::text || ', ' || 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$

LANGUAGE plpgsql;

thank you. but i'm new to postgres and don't know how to write select into ... or perform select (discarding result). can you write part of code that is incorrect??? — Chernusha
– Chernusha, Commented Sep 25, 2017 at 12:37
error happens at SELECT LogicID = "LogicID" ,SheetName = "SheetNa.....WHERE "LogicID" = INC; - you ether should "put" result somewhere or discard it. from your code I assume you at all wanted to assign thiat sql as text value to variable sql. but I cant tell it -because your post lacks comments — Vao Tsun
– Vao Tsun, Commented Sep 25, 2017 at 12:39
select into does not work with already existing table? am i right? — Chernusha
– Chernusha, Commented Sep 25, 2017 at 12:42

Vao Tsun · Accepted Answer · 2017-09-25 13:05:11Z

0

assign value with VAR_NAME := ''; construct, here is an example:

t=# create function f(_i int) returns table (a int) as $$
declare sql text;
begin
 sql := format('select %s',_i);
 return query execute sql;
end;
$$
language plpgsql
;
CREATE FUNCTION
t=# select * From f(3);
 a
---
 3
(1 row)

answered Sep 25, 2017 at 13:05

Vao Tsun

52.5k13 gold badges114 silver badges149 bronze badges

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

3 Comments

Chernusha Over a year ago

thank you. i've successfully assigned values to variables.now, problem is "ERROR: cannot open INSERT query as cursor".

Vao Tsun Over a year ago

post the code, error and question as a new post please

Chernusha Over a year ago

stackoverflow.com/questions/46423638/…

Collectives™ on Stack Overflow

postgresql insert dynamic query result into table in cursor

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related