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

Question

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?

Try posting something other than raw code, such as a description of what you tried/read, where you get stuck, error messages, etc. Also, please format your code in future. — Craig Ringer
– Craig Ringer, Commented Sep 29, 2017 at 5:00
What I want To achieve is basically; to be able to check the return value from "second" function and do a condition "first" function based on "second"'s result. I have tried Selecting*FROM data.second() as dat, but Im getting " query has no destination for result data" . Sorry for the unformatted code — Pickles
– Pickles, Commented Sep 29, 2017 at 5:12
A google search for the error message will be informative in this case, try starting there. Look into PERFORM and the SELECT ... INTO option, and FOUND variable, as discussed in the plpgsql manual. — Craig Ringer
– Craig Ringer, Commented Sep 29, 2017 at 6:36

Vao Tsun · Accepted Answer · 2017-09-29 07:21:15Z

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related