2

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?

1
  • postgres needs to know the type at call time.
    – Jasen
    Jul 31 '18 at 20:53
3

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:

1
  • Okay, so the idea is to use text[] as a fixed return type, then interpret the returned data based on _info on the caller side. This makes sense if postgres really needs to know the return type at call time. And it only costs one extra cast ::text[] on each call. Thank you :)
    – iago-lito
    Aug 1 '18 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.