INSERT INTO temp table not working in stored procedure

Question

This script works fine when running on dbeaver, I can work with the new created temp table:

SELECT someField
INTO TEMP tmp_TableZZ 
FROM "_fdw_xxx".myTable;

But when inside a stored procedure, I got this error message:

SQL Error [42601]: ERROR: "temp" is not a known variable

Same code:

CREATE OR REPLACE procedure PopulateSomething() 
  LANGUAGE plpgsql
AS $procedure$
    DECLARE v_ReportDte date;
begin

--some code omitted

    SELECT someField
    INTO TEMP tmp_TableZZ 
    FROM "_fdw_xxx".myTable;

--some code omitted

end; $procedure$
;

Using TEMPORARY instead of TEMP got the same result.

That's one of the reasons why the standard compliant `create table new_table as select ...` is recommended over `select ... into new_table ..` — , May 12 '22 at 13:06

score 1 · Answer 1 · answered May 12 '22 at 13:33

1

well, I find out that SELECT INTO in SQL is different from SELECT INTO in pgsql. The former accepts TEMP as the parameter, the latter expects a variable to store some value.

answered May 12 '22 at 13:33

German EP

11
2

INSERT INTO temp table not working in stored procedure

1 Answers1