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

user_17626612 0
Viewed 28

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

postgresql sql
1 Answers

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;
user_3303150
Related