How to return multiple values from a Postgres function?

Question

I'm using this function in PostgreSQL and it works but now I need to return multiple values, e.g.: _value and _prefix.

How can I do?

CREATE OR REPLACE FUNCTION generate_sequence(_account_id integer, _sequence text) RETURNS TEXT AS $$
DECLARE
  _prefix text;
  _next_value text;
  _zero_pad integer;
  _value text;
BEGIN
  SELECT asq.prefix, asq.next_value::text, asq.zero_pad
  INTO _prefix, _next_value, _zero_pad
  FROM account_sequence asq
  WHERE asq.account_id = _account_id
    AND asq.sequence = _sequence;

  _value := _prefix || _next_value;

  IF _zero_pad IS NOT NULL THEN
    _value := lpad(_value, _zero_pad, '0');
  END IF;

  UPDATE account_sequence SET
    next_value = next_value + 1
  WHERE account_id = _account_id
    AND sequence = _sequence;

  RETURN _value;
END;
$$ LANGUAGE plpgsql;

Pavel Stehule · Answer 1 · 2021-01-02T05:36:00.530

You should to use OUT parameters:

CREATE OR REPLACE FUNCTION fxreturns2(IN a int, OUT b int, OUT c int)
AS $$
BEGIN
  b := a + 1;
  c := a + 2;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

DO $$
DECLARE r record;
BEGIN
  r := fxreturns2(10);
  RAISE NOTICE 'b=% c=%', r.b, r.c;
END;
$$;

or

SELECT fxreturns2(10);
┌────────────┐
│ fxreturns2 │
╞════════════╡
│ (11,12)    │
└────────────┘
(1 row)

SELECT * FROM fxreturns2(10);
┌────┬────┐
│ b  │ c  │
╞════╪════╡
│ 11 │ 12 │
└────┴────┘
(1 row)

Please read plpgsql documentation. This topic and others are well described there, and it is maybe less 50 pages.

Sometimes better is returning value of composite type. The composite type ensure consistency in all code:

CREATE TYPE typ2 AS (b int, c int);

CREATE OR REPLACE FUCNTION fxtyp2(a int)
RETURNS typ2 AS $$
DECLARE r typ2;
BEGIN
  r.b := a + 1;
  r.c := a + 2;
  RETURN r;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

The usage is same like when you use OUT parameters.

Your code is not safe for concurrent usage. You should to read some about race condition. You should to use ISOLATION LEVEL SERIALIZABLE or you should to force row level locks - SELECT FOR UPDATE. Any selected rows that will be updated should be locked against current write.

How to return multiple values from a Postgres function?

1 Answers1