How to insert a parameter value in insert query in PostgreSQL function?

Question

I wrote a function in PostgreSQL with a parameter, where I want to insert the parameter value in a database table. The script executes fine but when I call the function I got an error message:

CREATE OR REPLACE FUNCTION sp_load_purchase_order(x int)
  RETURNS void AS
$$ 
declare var_val char;
begin

var_val='p'+i;
insert into purchase_order(
create_uid, 
   create_date, 
   write_date, 
   write_uid, 
   journal_id, 
   date_order, 
   partner_id, 
   amount_untaxed, 
   location_id, 
   company_id, 
   amount_tax, 
   state, 
   pricelist_id, 
   warehouse_id, 
   payment_term_id, 
   amount_total, 
   name, 
   invoice_method, 
   shipped, 
   minimum_planned_date
) 
values(1,now(),now(),1,13,now(),17,1.00,12,1,0.00,'draft',2,1,3,1.00
      ,var_val,'order','f' ,now()
);

end;
$$
  LANGUAGE 'plpgsql';

Error message:

ERROR:  column "i" does not exist
LINE 1: SELECT 'p'+i
                   ^
QUERY:  SELECT 'p'+i
CONTEXT:  PL/pgSQL function sp_load_purchase_order(integer) line 5 at assignment

********** Error **********

ERROR: column "i" does not exist
SQL state: 42703
Context: PL/pgSQL function sp_load_purchase_order(integer) line 5 at assignment

Please help me point out the problem.

Community · Accepted Answer · 2017-05-23 12:13:59Z

2

And the assignment operator in plpgsql is :=:
The forgotten assignment operator "=" and the commonplace ":="

And do not quote the language name plpgsql!

And you do not need to declare a variable for that. The concatenation can take place in the INSERT statement, which is cheaper.

CREATE OR REPLACE FUNCTION sp_load_purchase_order(x int)
  RETURNS void AS
$func$ 
begin

insert into purchase_order(create_uid, ..., name, ...) 
values(1, ..., 'p' || x, ...);

end
$func$  LANGUAGE plpgsql;

edited May 23, 2017 at 12:13

CommunityBot

11 silver badge

answered Dec 24, 2013 at 15:44

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian A. Henning · Accepted Answer · 2013-12-24 15:12:28Z

0

Your function parameter is named x but your function body refers to an undeclared variable named i...

answered Dec 24, 2013 at 15:12

Brian A. Henning

1,57710 silver badges32 bronze badges

Comments

jay · Accepted Answer · 2013-12-24 15:28:59Z

I've done two bad mistakes in my script... one was that undeclared 'i' variable, and another one was my concatenating attempt where i used '+' sign... here is the working code::::

CREATE OR REPLACE FUNCTION sp_load_purchase_order(x int)
  RETURNS void AS
  $$ 
  declare var_val char(10);
begin

var_val='p'||x;
insert into purchase_order(
create_uid, 
   create_date, 
   write_date, 
   write_uid, 
   journal_id, 
   date_order, 
   partner_id, 
   amount_untaxed, 
   location_id, 
   company_id, 
   amount_tax, 
   state, 
   pricelist_id, 
   warehouse_id, 
   payment_term_id, 
   amount_total, 
   name, 
   invoice_method, 
   shipped, 
   minimum_planned_date
) 
values(
1,now(),now(),1,13,now(),17,1.00,12,1,0.00,'draft',2,1,3,1.00,var_val,'order','f' ,now()
);


end;
$$
  LANGUAGE 'plpgsql';

Collectives™ on Stack Overflow

How to insert a parameter value in insert query in PostgreSQL function?

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related