PostgreSQL – Return Both One Value and One Column from pl/pgsql Function

datatypesplpgsqlpostgresqlrecord

I wish my pl/pgsql function to go fetch and return two informations in my database in a single connection:

  • one vector containing data whose type is unknown at call time (an array)
  • one string value containing information as how to interpret this data

Here is an illustration of the function architecture:
What should I use as a return type / return value / return instruction so both these informations are returned?

CREATE OR REPLACE FUNCTION public.retrieve_vector(
    vname bigint)  -- the vector name: actually a column name
    RETURNS RECORD -- * * * is that the return type I need?  * * *
    LANGUAGE 'plpgsql'
    VOLATILE
AS $BODY$
DECLARE
    info text; -- data interpretation information
BEGIN

-- Retrieve data information from, say, the such-information table
SELECT info FROM public."InfoTable"
    WHERE name = vname
    INTO info; -- (this is actually a more complicated process)

-- Retrieve the vector and return both
RETURN info,
      (SELECT vname FROM public."Vectors"); -- (this is also more complicated)
-- * * * but RETURN a, b; is not possible like this, right? How to do? * * *

END
$BODY$;

As dummy instances, I expect this function to return something like retrieve_vector('temperatures') -> ['old thermometer', [0, 1, 5, -3]] or retrieve_vector('words') -> ['small words only', ['at', 'the', 'and', 'or']].
(To those who might ask: in reality this is about meta-programming, so the "info" is actually refined type information.)

In such a situation, I think that:

  • it is legitimate that this function returns two values
  • it is legitimate that the vector type is unknown at call time
  • it is legitimate that the first value is scalar (single value) and the other is a vector (a column)
  • it is legitimate wanting to connect the database only once

Is it possible defining a function with such a flexible signature?
How would I do this?

Best Answer

but RETURN a, b; is not possible like this, right? How to do?

Right, not like this. But this is possible:

CREATE OR REPLACE FUNCTION public.retrieve_vector(
   _vname bigint
 , OUT _info text       -- "data interpretation information"
 , OUT _arr  text[]     -- "vector"
   )
  RETURNS record AS     -- that is the return type you need
$func$
BEGIN
   -- Retrieve data information
   SELECT info
   FROM   public."InfoTable"
   WHERE  name = _vname
   INTO   _info;        -- actually a more complicated process

   -- Retrieve "vector"
   SELECT vname         -- you may need explicit casting to text[] ...
   FROM   public."Vectors"
   INTO   _arr;         -- also actually more complicated

   -- RETURN;           -- optional
END
$func$  LANGUAGE plpgsql;

Don't quote the language name plpgsql.

Assuming you want to return a single row. For returning 0-n rows (a set) you would use RETURNS TABLE(_info text,_arr text[]) in combination with RETURN NEXT or RETURN QUERY or similar. See:

The term "vector" in your question does not help to clarify which actual data types we are dealing with. You also mention "array". Any Postgres array can be cast to text[], so I use that type as common denominator. PL/pgSQL SELECT INTO coerces compatible types into the target data typesd automatically. Else you may need to add an explicit cast like source_array_column::text[]. Not enough info in your question to narrow this down.

You asserted that:

type is unknown at call time

Else you might be able to work with a polymorphic array type as well. See:

Related Question