CODEWITHSUNDEEP
postgresqlfunction
small pig
small pig

Reputation: 19

postgres how get get multiple columns values?

CREATE OR REPLACE FUNCTION "public"."sxfun"("jcbh" text)
  RETURNS "pg_catalog"."int4" AS $BODY$
declare leftplayer TEXT;
declare rightplayer TEXT;
declare leftcoin int;
    BEGIN
    SELECT player1 into leftplayer,player2 into rightplayer FROM table1 WHERE id=$1;
    SELECT SUM(playcoin) into leftcoin FROM table2 WHERE playname=leftplayer 
    COMMIT;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100

this code syntax error,let how to solve it,please

Upvotes: 1

Views: 127

Answers (1)

user330315
user330315

Reputation:

You are missing a return statement. In PL/pgSQL, declare starts a block, there is no need to repeat the keyword for every variable. And you can't commit in a function - and you don't need it to begin with.

As documented in the manual you need to use return to return a value from a function.

To store multiple columns into multiple variables, you need to separate them with a comma, not repeat the INTO clause.

Note that sum() returns a bigint, so your variable and return type should also be defined as bigint.

CREATE OR REPLACE FUNCTION public.sxfun(jcbh text)
  RETURNS bigint
AS 
$BODY$
declare 
  leftplayer TEXT;
  rightplayer TEXT;
  leftcoin bigint;
BEGIN
   SELECT player1, player2  
    into leftplayer, rightplayer 
  FROM table1 
  WHERE id = jcbh;    
  SELECT SUM(playcoin) 
     into leftcoin 
  FROM table2 
  WHERE playname = leftplayer;

  return leftcoin; --<< return the value
END
$BODY$
LANGUAGE plpgsql;

If id is a number (which the name usually indicates), the parameter jcbh should be declared as integer, not as text.

Note that you can simplify this to a single statement. There is no need for intermediate variables:

CREATE OR REPLACE FUNCTION public.sxfun(jcbh text)
  RETURNS bigint 
AS 
$BODY$
  SELECT SUM(playcoin) 
  FROM table2 
  WHERE playname IN (select leftplayer
                     FROM table1 
                     WHERE id = jcbh); 
$BODY$
LANGUAGE sql;

Upvotes: 1

Related Questions