CODEWITHSUNDEEP
postgresql
Venkatesh Deo
Venkatesh Deo

Reputation: 39

I am not able to insert values into temporary table in a postgres function

I have created a temporary table in PostgreSQL

create temporary table tempTable(
val int
)

I have another table newTable which has the following data:

val
5
6
0
8
7
0
9
2 
0

But my function

create or replace function getval(
out valOut int
)
As $$
Begin
    insert into newTable values(5) returning inserted.val into tempTable             
values(inserted.val);
End; $$
language plpgsql;

I am getting the following error:

ERROR: "temptable" is not a known variable

LINE 6: ...to newTable values(5) returning inserted.val into tempTable ...

Can you please help me with this. Thanks.

Upvotes: 1

Views: 1095

Answers (1)

user330315
user330315

Reputation:

You can't specify a table name with returning.

In order to do that, you need to chain two CTEs:

with new_rows as (
  insert into newtable (val) values (5)
  returning *
)
insert into temptable (val)
select val
from new_rows;

Upvotes: 1

Related Questions