Return record with column definition from a function

Question

I know that Postgres allows to return a predefined composite datatype, a record and a table with column definition.

However I did not manage to return a simple record with defined columns so I always use tables even if I know that this will only return one line.

So my function definition looks something like this:

CREATE OR REPLACE FUNCTION name(p_param1 text, p_param2 json)
    RETURNS TABLE(
       col1    bigint,
       col2    integer,
       col3    integer
    )

If I try do return a record the same way it fails:

CREATE OR REPLACE FUNCTION name(p_param1 text, p_param2 json)
    RETURNS RECORD(
       col1    bigint,
       col2    integer,
       col3    integer
    )

Is this not possible in Postgres? I know that I can just declare a custom composite data type but I don't want to declare a type for every function returning a record.

What about using OUT parameters? Is that what you're looking for? — Frank Heikens

Erwin Brandstetter Erwin Brandstetter · Accepted Answer · 2014-07-29T14:41:38

You are looking for OUT parameters:

CREATE OR REPLACE FUNCTION name(
        p_param1 text
  ,     p_param2 json
  , OUT col1 bigint
  , OUT col2 integer
  , OUT col3 integer)
  RETURNS record AS ...

The difference: this form returns exactly one row, while RETURNS TABLE returns 0-n rows.
More:

Returning from a function with OUT parameter

Return record with column definition from a function

1 Answers