Sample Header Ad - 728x90

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