Return a record from function

Question

I'm trying to return multiple values in plpgsql with a very simplistic test function. The test function takes an integer as input and is supposed to return if it's positive, negative and zero. I've seen some example but they are all over complicated and go over my head.

Here is what i tried:

create or replace function test(v integer)
returns record as $$

BEGIN

    IF v > 0
        THEN return true as positive, false as negative, false as zero;
    ELSEIF v < 0
        THEN return false as positive, true as negative, false as zero;
    ELSE 
        return false as positive, false as negative, true as zero;
    END IF;

END;
$$ LANGUAGE plpgsql;

Also tried this

create or replace function test(v integer)
returns record as $$

DECLARE 
    result record;

BEGIN

    IF v > 0
        THEN 
            result.positive = true;
            result.negative = false;
            result.zero = false;

    ELSEIF v < 0
        THEN 
            result.positive = false;
            result.negative = true;
            result.zero = false;
    ELSE 
            result.positive = false;
            result.negative = false;
            result.zero = true;
    return result;
    END IF;

END;
$$ LANGUAGE plpgsql;

ALSO this:

IF v > 0
    THEN 
        SELECT true, false, false into
        result.positive, result.negative, result.zero;

ELSEIF v < 0
    THEN 
        SELECT false, true, false into
        result.positive, result.negative, result.zero;
ELSE 
        SELECT false, false, true into
        result.positive, result.negative, result.zero;
return result;
END IF;

Answer 1

Here's a solution to the "return multiple values" problem that works for me.

Frankly, it took WAAAAAY too long to find a solution to such a common need! Like the OP, most examples I've seen are over complicated and go over my head. And of course the only reason I'm even looking for examples is because the docs aren't clear to this beginner. Here's hoping this will help someone.

Paste the code below into a pgAdmin query window, choose one of the invocation solutions, then execute.

create or replace function receive_two_values() returns void language plpgsql as
$main$
declare
    returned record;
begin

    create or replace function return_two_arguments(
            arg1    text,
            arg2    text,
        out return1 text,
        out return2 text)
        language plpgsql as --"Returns record" is implied by multiple outs, so unnecessary here.
    $$
    begin
        return1 := arg1;
        return2 := arg2;
    end;
    $$;

    returned = return_two_arguments('Hello', 'World');
    raise notice 'return1="%"', returned.return1;
    raise notice 'return2="%"', returned.return2;

end;
$main$;

--Invoke in one of at least two ways:

--ONE:
    select receive_two_values();

--TWO:
    do language plpgsql
    $$
    begin
        perform receive_two_values();
    end;
    $$;


--Outputs:
--NOTICE:  return1="Hello"
--NOTICE:  return2="World"

Answer 2

I would solve the problem as follows:

CREATE TYPE sign AS ENUM ('negative', 'zero', 'positive');

CREATE FUNCTION test(integer) RETURNS sign
   LANGUAGE sql IMMUTABLE STRICT AS
$$SELECT CASE
            WHEN $1 < 0
            THEN 'negative'
            WHEN $1 > 0
            THEN 'positive'
            ELSE 'zero'
         END::sign$$;

If you want to return a record like you do in your examples, you'll have to use the ROW() constructor to create a single composite value from the three booleans:

RETURN ROW(true, false, false);

But this solution has its drawbacks: you cannot access the individual fields of the composite type in SQL, because PostgreSQL doesn't know the fields at parse time:

test=> SELECT test(42);
┌─────────┐
│  test   │
├─────────┤
│ (t,f,f) │
└─────────┘
(1 row)

test=> SELECT (test(42)).*;
ERROR:  record type has not been registered

test=> SELECT * FROM test(42);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM test(42);
                      ^

You'd have to specify the record definition in the query:

test=> SELECT positive FROM test(42) test(positive boolean, negative boolean, zero boolean);
┌──────────┐
│ positive │
├──────────┤
│ t        │
└──────────┘
(1 row)

But that kind of defies your goal of variable records. This is why such functions are less useful than the answer you referenced tries to make believe.

Answer 3

This will return a record, so the result will be ( true, false, false).

CREATE OR REPLACE FUNCTION test(v integer)
  RETURNS record AS $$

DECLARE 
    result record;

BEGIN
        SELECT true , false , false  into result;
return result;

END;

The issue is that the result isn't named so you have this ( true, false, false) and you want to get each value separately. To do so you can do this:

select a, b, c from test(1) AS (a BOOL, b bool, c bool);