Not able to call multiple functions from one function in PostgreSQL

Question

Here are my two functions,

CREATE OR REPLACE FUNCTION public.insert_stagging()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
      BEGIN
         INSERT into stagging(data)
         select data from mytable where data not like 'table%';
      END;
  $function$;

CREATE OR REPLACE FUNCTION clear_mytable2() 
    RETURNS TABLE(data1 text) as
$BODY$   
BEGIN
    RETURN QUERY (select data from mytable2);
END;
$BODY$
    LANGUAGE plpgsql;

Not able to call above two functions from another function,

CREATE OR REPLACE FUNCTION func_test()
  returns void as
$func$
BEGIN
    EXECUTE 'SELECT insert_stagging()';
    EXECUTE 'SELECT clear_mytable2()';
END
$func$ LANGUAGE plpgsql;

With the above code, I am only able to get the execution result of 1st function written inside the BEGIN clause, if I change the sequence of the 'SELECT insert_stagging()'; with 'SELECT clear_mytable2()'; then I'll only get the execution result from clear_mytable2() function.

How to execute both the function calls within 1 PostgreSQL function?

Pavel Stehule · Accepted Answer · 2021-10-01 07:39:08Z

1

When the function returns table, then returns result just to caller function. When the caller function ignores this result, then result is thrown.

If you want to call void function, then you should to use PERFORM. Using EXECUTE in this context is bad idea. This is designed to work with dynamic SQL. If you want to call table function, you have to use RETURN QUERY again:

CREATE OR REPLACE FUNCTION func_test()
  RETURNS TABLE(data1 text) AS
$func$
BEGIN
    PERFORM insert_stagging();
    RETURN QUERY SELECT * FROM clear_mytable2();
END
$func$ LANGUAGE plpgsql;

SELECT * FROM func_test();

answered Oct 1, 2021 at 7:39

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Not able to call multiple functions from one function in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related