How to return a multiple tables result in stored procedure or function in Postgresql?

In PostgreSQL, you can use a function that returns a set of rows or multiple result sets using the RETURNS TABLE syntax. Here is an example of how you can create a function that returns multiple tables:

Let’s say you want to return two tables with different structures. You can define a custom type for each table, and then the function can return a set of these custom types.

-- Create a custom type for the first table
CREATE TYPE table1_type AS (
    id INTEGER,
    name VARCHAR
);

-- Create a custom type for the second table
CREATE TYPE table2_type AS (
    product_id INTEGER,
    quantity INTEGER
);

-- Create the function that returns multiple tables
CREATE OR REPLACE FUNCTION get_multiple_tables()
RETURNS TABLE (
    table1_result table1_type,
    table2_result table2_type
) AS $$
BEGIN
    -- Query for the first table
    table1_result := (
        SELECT id, name
        FROM your_table1
    );

    -- Query for the second table
    table2_result := (
        SELECT product_id, quantity
        FROM your_table2
    );

    -- Return the results
    RETURN NEXT;
END;
$$ LANGUAGE plpgsql;

In this example:

table1_type and table2_type are custom types representing the structure of your tables.
The get_multiple_tables function returns a set of rows with two columns, each of the custom types.
Inside the function, you can perform queries for each table and assign the results to the corresponding variables of custom types.
To call this function:

SELECT * FROM get_multiple_tables();

Remember to replace your_table1 and your_table2 with the actual names of your tables.

This is just a basic example, and you might need to adjust it based on your specific use case and table structures.