SQL Error [42P01]: ERROR: relation “temp_table_name” does not exist

Question

I am trying to create a temporary table in my stored function in Postgresql. The language I am using is "plpgsql". When I run the stored function it throws an SQL Error [42P01].

Exact Error

: "Query execution failed . Reason:
SQL Error [42P01]: ERROR: relation "temp_table" does .
not exist"

CREATE OR REPLACE FUNCTION public.sp_str_dsl_lp_ung(x int8)
RETURNS integer
LANGUAGE plpgsql volatile
AS $function$
declare 

--variables
variab int :=0;
begin  

    CREATE temporary TABLE temp_table  (a int8, b int8) ;
    insert into temp_table (a,b) select (a,b) from existing_table;
end
$function$

I want to create a temp table to store some records temporarily.

Kaushik Nayak Kaushik Nayak · Accepted Answer · 2019-01-16T07:43:50

You have few issues with your code

You have defined the function as stable which, as per documentation is not allowed and you would receive an error while executing.

ERROR: CREATE TABLE is not allowed in a non-volatile function

To fix this, you must change it to VOLATILE

insert into temp_table (a,b) select (a,b) doesn't work because parenthesising the columns implies that you are treating them as record and not individual columns.

Fixing these two, your function should work fine.

CREATE OR REPLACE FUNCTION sp_str_dsl_lp_ung(color smallint,
lpcount integer, update_userid character varying, OUT lp integer)
RETURNS integer
LANGUAGE plpgsql volatile
AS $function$
declare 

--variables
variab int :=0;
begin  

    CREATE temporary TABLE temp_table  (a int8, b int8) ;
    insert into temp_table (a,b) select a,b from existing_table;
end
$function$

Demo

SQL Error [42P01]: ERROR: relation “temp_table_name” does not exist

1 Answers