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?