Creating a function which returns a table where different columns come from different tables

Question

I would like to write a function in Postgresql with 2 parameters which returns a table. This table will have two columns (column1 and column2), and for each of these columns I would need to get information from a column coming from another table (table1 or table2) according to the parameters of the function (param1 and param2). Something like this:

CREATE FUNCTION trial(param1 VARCHAR(50), param2 VARCHAR(50))
RETURNS TABLE (
    column1 VARCHAR(50),
    column2 VARCHAR(50)

AS $$
    BEGIN 
    RETURN QUERY 
        SELECT c1 as column1 FROM table1 WHERE column1 = param1;
        SELECT c2 as column2 FROM table2 WHERE column2 = param2;
    END;    
$$LANGUAGE plpgsql;

My problem is that if I run this function with the two parameters, for example

SELECT trial('car', 'house');

or

SELECT * FROM trial('car', 'house');

I get an error like this:

ERROR:  structure of query does not match function result type
DETAIL:  Number of returned columns (1) does not match expected column count (2).

I can imagine that the problem is the way I do the QUERY, but I can't find the proper manner to do it. How can I query from 2 tables to fill my column1 and column2, if the two tables from which I want to query have nothing in common and I can't do a JOIN?

Thanks a lot in advance!

Sergio

user330315 · Accepted Answer · 2021-12-08 15:12:40Z

1

Your code only returns the result of a the first query, the second query is no longer part of the return query.

You need to write one query with one scalar sub-select for each column you want to return

select (SELECT c1 as column1 FROM table1 WHERE column1 = param1), 
       (SELECT c2 as column2 FROM table2 WHERE column2 = param2);

You also don't need PL/pgSQL for this. A language sqlfunction will do just fine and is a bit more efficient:

CREATE FUNCTION trial(param1 VARCHAR(50), param2 VARCHAR(50))
RETURNS TABLE (
    column1 VARCHAR(50),
    column2 VARCHAR(50)

AS $$
  select (SELECT c1 as column1 FROM table1 WHERE column1 = param1), 
         (SELECT c2 as column2 FROM table2 WHERE column2 = param2);
$$
LANGUAGE sql;

answered Dec 8, 2021 at 15:12

user330315

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

1 Comment

Sergio Valbuena Álvarez Over a year ago

This was exactly what I was looking for, thank you very much for your kind help.

Collectives™ on Stack Overflow

Creating a function which returns a table where different columns come from different tables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related