CODEWITHSUNDEEP
sqlpostgresqlfunctionplpgsql
Pickles
Pickles

Reputation: 243

How Do I use or properly return a value from my PostgreSQL function?

CREATE OR REPLACE FUNCTION data.first()
AS $BODY$
DECLARE
  res numeric;
BEGIN
  PERFORM data.second(5,3,4);
  IF(res > 10)THEN
    something
  ELSEIF(res < 10)THEN
    something else
  END IF
END;
$BODY$;

=========================================

CREATE OR REPLACE FUNCTION data.second(
a numeric,
b numeric,
c numeric
OUT res numeric
)
RETURNS numeric
AS $BODY$
BEGIN
  res = a + b;
END;
$BODY$;

How do I use res in the parent function?

Upvotes: 0

Views: 130

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51406

don't specify both OUT and function returns:

t=# CREATE OR REPLACE FUNCTION data.second(
a numeric,
b numeric,
c numeric,
OUT res numeric
)
AS $BODY$
DECLARE
BEGIN
res = a + b;
END;
$BODY$ language plpgsql;
CREATE FUNCTION

if you want to use the return of function ,use select into VAR, perform will just execute function discarding its output:

t=# CREATE OR REPLACE FUNCTION data.first() returns text
AS $BODY$
DECLARE
res numeric;
BEGIN
SELECT data.second(5,3,4) INTO res;
IF(res > 5)THEN
  raise info 'second returned %',res;
END IF;
RETURN 'here is the return';
END;
$BODY$ language plpgsql;
CREATE FUNCTION

finaly:

t=# select * from data.first();
INFO:  second returned 8
       first
--------------------
 here is the return
(1 row)

https://www.postgresql.org/docs/current/static/plpgsql.html

Upvotes: 1

Related Questions