CODEWITHSUNDEEP
postgresqlreturn-valuerecorduser-defined-functionsplpgsql
user3347114
user3347114

Reputation: 185

Return record with column definition from a function

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.

Upvotes: 1

Views: 1316

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

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:

Upvotes: 2

Related Questions