How to declare a variable as a result of a function?

Question

I am currently making a function, and I need to declare a variable as a result of an other function within my main_function.

CREATE OR REPLACE FUNCTION main_function(t_name varchar) 
RETURNS void AS 
$BODY$
DECLARE
    var_1 varchar := execute format('select var_1 from sub_function(%s)' ,t_name);
BEGIN
    --do something with var_1
END;
$BODY$
LANGUAGE plpgsql;

My sub_function returns one row with three colums.

select var_1 from sub_function()

returns only one result. I would like to store that result in the variable var_1 because I will need it later. It is also important that this sub_function operates with the t_name variable, which I get from the main_function's argument. I have tried to do it in many different ways, for example without the execute function.

var_1 varchar := format('select var_1 from sub_function(%s)' ,t_name);

Unfortunately this one returns the whole text "select var_1 from sub_function('soimething')" and not the result of the query. What should I do? Thanks for any help in advance!

Dynamic SQL is completely unnecessary here. – Apr 24 '20 at 07:32 — , Apr 24 '20 at 07:32

score 2 · Accepted Answer · answered Apr 24 '20 at 07:31

return_column_name is the column your function should return. I don't know the name since you have said your function returns 3 columns. FYI, you can get all three values by using select ... into va1, var2, var3

CREATE OR REPLACE FUNCTION main_function(t_name varchar) 
RETURNS void AS 
$BODY$
DECLARE
    var_1 varchar;
BEGIN
    select <return_column_name> from sub_function(t_name)  into var_1;
END;
$BODY$
LANGUAGE plpgsql;

Link to doc

How to declare a variable as a result of a function?

1 Answers1