How to return Select * INTO STRICT variable from function

Question

I am trying to return a single row of a table in a function and it is important that if there isn't any row or more then one there is an exception raised. I am using SELECT * INTO STRICT for that reason. However, I can't seem to find the correct way to return. Was wondering if someone knew the correct way of returning the single row?

I know I can separate the check and get it work but was curious if there was a way to get this to work.

CREATE OR REPLACE FUNCTION GameInfo.getAllPlayerInfo(
  playerID    GameInfo.Player.PID%Type)
RETURNS TABLE (PID VARCHAR,Name VARCHAR, Email VARCHAR,Password VARCHAR) AS
 $$
 DECLARE
found_player GameInfo.Player%ROWTYPE;
BEGIN
     SELECT * INTO STRICT found_player FROM GameInfo.Player WHERE Player.PID = 
 $1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RAISE EXCEPTION 'PID % not found';
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'PID % not unique';
RETURN found_player;
END;
$$ LANGUAGE plpgsql
STABLE
SECURITY DEFINER;

user330315 · Accepted Answer · 2019-07-21 07:06:46Z

Your function is declared as returns table, so in PL/pgSQL you need to use return next... to return a row.

That return statements needs to be moved before the exception handling. Currently your function does not return anything, if a row is found.

You can also simplify the function declaration by using returns setof so you don't need to specify all columns.

CREATE OR REPLACE FUNCTION GameInfo.getAllPlayerInfo(playerID GameInfo.Player.PID%Type)
  RETURNS setof gameinfo.player
AS $$
DECLARE
  found_player GameInfo.Player%ROWTYPE;
BEGIN
  SELECT * 
     INTO STRICT found_player 
  FROM GameInfo.Player 
  WHERE Player.PID = playerid;

  RETURN NEXT found_player; --<< here
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      RAISE EXCEPTION 'PID % not found';
   WHEN TOO_MANY_ROWS THEN
      RAISE EXCEPTION 'PID % not unique';
END;
$$ 
LANGUAGE plpgsql
STABLE
SECURITY DEFINER;

Note that set returning functions need to be used in the FROM clause:

select * 
from gameinfo.getallplayerinfo(1);

Collectives™ on Stack Overflow

How to return Select * INTO STRICT variable from function

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