PostgreSQL: Is it possible to get a different function result type (i.e. TABLE) in stored function based on input

postgresqlset-returning-functionsstored-procedures

The question is as follow, I wanted to get some flexibility in my example function, so based on input arguments to function I wanted to return different table as an output result.

So is it possible to get something like this in function definition:

RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)

OR

RETURNS TABLE (
    message VARCHAR
)

My broken example function is:

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR, 
    id_flag BOOLEAN
)
RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)
AS
$$
<<function_body>>
DECLARE
BEGIN
  IF id_flag THEN
      RETURN QUERY 
        SELECT 
          cstmr.customer_id,
          cstmr.first_name,
          cstmr.last_name,
          cntr.country
        FROM 
          customer AS cstmr
        JOIN address ON cstmr.address_id = address.address_id
        JOIN city ON address.city_id = city.city_id
        JOIN country AS cntr ON city.country_id = cntr.country_id
        WHERE 
        cntr.country = country_name;
    ELSE
      RETURN QUERY 
        SELECT 'flag was not provided';
    END IF;
END function_body;
$$
LANGUAGE plpgsql;

SELECT get_customer_by_country('Belgium', false);

I searched for a questions like ERROR: structure of query does not match function result type, but haven't found anything that matched my case.

Thanks in advance!

Best Answer

First off, you should take a look at Erwin Brandstetter's answer to a similar question: https://stackoverflow.com/a/23957098/835781

If the return type you want is mimicked by the structure of an existing table, you could use that existing table's regclass to define the type of the results. That method seems to be a bit overkill for your use case, if I'm understanding it correctly.

My suggestion would be to just create two functions. If you need to keep the same name, I would suggest you overload the function definition:

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR
)
RETURNS TABLE (
    customer_id INTEGER,
    first_name VARCHAR(45),
    last_name VARCHAR(45),
    country VARCHAR(50)
)
AS
$$ ... $$ ;

CREATE OR REPLACE FUNCTION get_customer_by_country(
    country_name VARCHAR, 
    id_flag BOOLEAN
)
RETURNS TABLE (
    message VARCHAR
)
AS
$$ ... $$ ;

This results in you still having to create two function definitions. If you're going to create two separate functions, you should probably just use different names for each. If your application code is expecting the two different result sets depending on the flag provided, why not interpret that flag in your application code and use that to determine which function to call?

If you're dead-set on returning two different data structures depending on the value of a variable, you can't use the schema of an existing table to define the result type, and you're ok with the processing overhead, then you could create an array or json return type. This gives your function the flexibility to build out the result in whatever format you choose, but requires additional processing and logic handling to interpret. At the end of the day, it's usually just easier to create different functions for the different result types.

Related Question