CODEWITHSUNDEEP
postgresqlplpgsql
ffs
ffs

Reputation: 107

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

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.

Upvotes: 1

Views: 2428

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You have few issues with your code

  1. 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

  1. 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

Upvotes: 1

Related Questions