Return both one value and one column from pl/pgsql function
2
votes
1
answer
1110
views
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?
Asked by iago-lito
(123 rep)
Jul 8, 2018, 10:21 AM
Last activity: Jul 26, 2018, 12:35 PM
Last activity: Jul 26, 2018, 12:35 PM