ERROR: syntax error at or near "returning" on stored procedure - PostgreSQL 8.1.23

Question

I am having a stored procedure as follows:

 INSERT INTO bakersfun.orderhead 

     (order_id,order_dt, customer_id, route_id, routenum, ordertype, create_station_id, create_stationtype, create_time,create_user_id,tran_time, tran_user_id,station_id)

   values 

     (nextval('bakersfun.orderhead_order_id_seq'),$1, $2, $3, $4, $5, $6, $7, LOCALTIMESTAMP, $8, 

    default, default,$9) 

   returning  order_id;

While executing I am getting error as follows:

new.sql:15: ERROR: syntax error at or near "returning"
LINE 15: returning order_id;

PostgreSQL version : PostgreSQL 8.1.23

I noticed it works on PostgreSQL 8.4.20

What is the alternative so it could work on PostgreSQL 8.1.23.

http://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions and http://dba.stackexchange.com/questions/89643/return-the-id-after-insert-or-select i hope these two link will help you to find answer — Shubham Batra, Jan 15 '16 at 06:47

score 0 · Answer 1 · edited May 23 '17 at 11:46

0

DECLARE 
  l_id integer;   -- change data type according to you.
BEGIN

INSERT INTO bakersfun.orderhead 

     (order_id,order_dt, customer_id, route_id, routenum, ordertype, create_station_id, create_stationtype, create_time,create_user_id,tran_time, tran_user_id,station_id)

   values 

     (nextval('bakersfun.orderhead_order_id_seq'),$1, $2, $3, $4, $5, $6, $7, LOCALTIMESTAMP, $8, 

    default, default,$9)     
  returning order_id into l_id; --< store the returned ID in local variable
   return l_id; --< return this variable
END

for more reference check these two link

Is SELECT or INSERT in a function prone to race conditions?

https://dba.stackexchange.com/questions/89643/return-the-id-after-insert-or-select

edited May 23 '17 at 11:46

Community

1
1

answered Jan 15 '16 at 07:13

Shubham Batra

2,199
4
24
43

getting error as `SQL error: ERROR: syntax error at or near "integer" LINE 2: l_id integer; ^ ` – Santhucool Jan 15 '16 at 07:24
`order_id` is of type Serial. You can see that I am inserting `order_id` as `nextval('bakersfun.orderhead_order_id_seq')`, So it is `order_id` not required to specify as IN or OUT parameter. – Santhucool Jan 15 '16 at 07:31
can you create sql fiddle – Shubham Batra Jan 15 '16 at 07:35
It is having only version 9.3. In 9.3 all things will work fine. So cant be created – Santhucool Jan 15 '16 at 07:37

ERROR: syntax error at or near "returning" on stored procedure - PostgreSQL 8.1.23

1 Answers1