Call Function Postgresql

Question

I want to insert table lokasi from function... but when i call these function there are error... Please your answer

CREATE OR REPLACE FUNCTION insert_lokasi2
  (anip character varying, aeksemplar character varying)
RETURNS boolean AS
$BODY$

DECLARE
  eks integer;
  tot integer;
  nilai boolean;
  eks1 integer;
  eks2 integer;
  tot2 integer;

BEGIN

   select sum(CAST(eksemplar AS INT)) 
     INTO eks 
     from lokasi 
    where nip = anip;

   tot := eks + aeksemplar;

   select CAST(eksemplar AS INT) 
     INTO eks1 
     from sensus 
    where nip = anip;

   select CAST(eksemplar2 AS INT) 
     INTO eks2 
     from sensus 
    where nip = anip;

   tot2 := eks1 + eks2;

   IF (tot <> tot2) THEN
     nilai := false;
   else 
     nilai := true;
   END IF;

   RETURN nilai;

END 

$BODY$

  LANGUAGE 'plpgsql' VOLATILE

  COST 100;

  ALTER FUNCTION insert_lokasi2(character varying, character varying) OWNER TO postgres;

  select * from insert_lokasi2('10.1010.4703','1');

ERROR: operator does not exist: integer + character varying

LINE 1: SELECT   $1  +  $2 
                     ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
QUERY:  SELECT   $1  +  $2 
CONTEXT:  PL/pgSQL function "insert_lokasi2" line 12 at assignment

Paul Ruane · Accepted Answer · 2011-04-01 04:54:09Z

1

eks is an integer whilst aeksemplar is a string. You need a cast by your addition:

tot := eks + CAST(aeksemplar AS INT)

Better would be to either do all these castings at the top of the finction or, if possible, change the argument types so that the casting is unecessary.

edited Apr 1, 2011 at 4:54

answered Apr 1, 2011 at 4:45

Paul Ruane

38.7k14 gold badges66 silver badges86 bronze badges

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

Collectives™ on Stack Overflow

Call Function Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related