How to return result of dynamic SELECT inside a function in PostgreSQL?

Question

A very similar question here but not quite the same as this one.

I have a function that uses IF statements to determine what type of SELECT query to return.

How can I declare what a CREATE FUNCTION statment should return when I will never know the exact columns a SELECT query within it might return? That is, I can't setup a RETURNS TABLE declaration with a list of columns because I don't know which columns might come back. All I know is that I definitely will want a table of results to be returned.

Here is my function (uncompleted, pseudo):

CREATE OR REPLACE FUNCTION functiona(_url character varying DEFAULT NULL) 
RETURNS -- what type? if TABLE how do I know what columns to specify
    LANGUAGE plpgsql
AS
$$
DECLARE
    _urltypeid int;
BEGIN
    IF _url IS NOT NULL
    THEN
        _urltypeid := reference.urltype(_url);
        IF _urltypeid = 1
        THEN
            RETURN QUERY
                SELECT location, auxiliary, response FROM tablea -- unique columns from one table
        END IF;
    IF _urltypeid = 2
        THEN
            RETURN QUERY
                SELECT ip, location, host, authority FROM tableb -- unique columns from another table
        END IF;
    END IF;
END;
$$;

I come from a MS SQL Server background where I don't have to specify in the CREATE FUNCTIONstatement what I'm returning, hence this is very confusing for me.

You could create a user defined type that unifies the result and `RETURNS SETOF `. Not much different than `RETURNS TABLE` but you can modify the type in the future so that all functions that use it respect the change. In my opinion it's a bad idea to return two different things from a single function. You could also return the `_urltypeid` from this function and act accordingly on the result set in whatever called this. Consider writing 2 functions that handle each of these separately and handle that logic in the caller. — J Spratt, Jan 11 '21 at 20:28
@JSpratt I think you're right that each function should just return one resut that has consistent column output. — volume one, Jan 11 '21 at 23:34

score 2 · Answer 1 · answered Jan 11 '21 at 18:39

2

Not an answer, but an explanation of why answer from @JonathanJacobson will not work using a simple example:

\d animals
                      Table "public.animals"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null | 
 cond   | character varying(200) |           | not null | 
 animal | character varying(200) |           | not null | 

CREATE OR REPLACE FUNCTION public.animal(a_type character varying)
 RETURNS record
 LANGUAGE plpgsql
AS $function$
BEGIN
    SELECT row(id, cond, animal) FROM animals where animal = a_type;
END;
$function$

select * from animal('cat');
ERROR:  a column definition list is required for functions returning "record"
LINE 1: select * from animal('cat');

CREATE OR REPLACE FUNCTION public.animal(a_type character varying)
 RETURNS SETOF record
 LANGUAGE plpgsql
AS $function$
BEGIN
    RETURN QUERY
    SELECT id, cond, animal FROM animals where animal = a_type;
END;
$function$
;

 select * from animal('cat') as t(i integer, c varchar, a varchar);
 i |  c   |  a  
---+------+-----
 1 | fat  | cat
 2 | slim | cat
 6 | big  | cat

In order to use the output of a function returning a record or setof record you need to declare the output fields and types when you run the function.

answered Jan 11 '21 at 18:39

Adrian Klaver

15,886
2
17
28

You're right, the other answer is not any better than using RETURNS TABLE. – volume one Jan 11 '21 at 18:42
@volumeone how can you say that it's not better than a piece of code that can't even be compiled? Anyhow, you said nothing about how you were planning to use the function output so my answer was as valid as any. It all depends on your needs. Clarify how you're going to use the function output and perhaps someone else will be willing to help you with that. – Jonathan Jacobson Jan 11 '21 at 18:53
Adrian, this debate is becoming more and more ridiculous. The OP requested a dynamic-schema solution for a strict relational DB. Of course the solution would require some sort of dynamic construct which in turn would require the end-user to be able to determine what the data elements really are. As I mentioned in my answer, you could use `jsonb`. Perhaps it would be better because then the meaning of every entity would be loosely defined by it's name. I've lost interest in this question/OP so will leave a `jsonb` implementation to someone more willing. – Jonathan Jacobson Jan 11 '21 at 19:02
@JonathanJacobson What I meant was that if I have to define a set list of columns at the outset, then RETURN TABLE would suffice. Your answer which is most appreciated does not offer anything more than RETURN TABLE would do. Of course jsonb etc probably is the way to go, but I think its better to just split out the function into other functions that return only consistent set of results. – volume one Jan 11 '21 at 23:37
The issue with returning `json/jsonb` is that if are going to use this in SQL to expand to a record, you will again need to know and output type or specify columns and types. – Adrian Klaver Jan 11 '21 at 23:46

Jonathan Jacobson · Answer 2 · 2021-01-11T18:19:28.160

-2

You could use the record type. Not tested.

CREATE OR REPLACE FUNCTION functiona(_url character varying DEFAULT NULL)
RETURNS record
    LANGUAGE plpgsql
AS
$$
DECLARE
    _broadcasttypeid int;
BEGIN
    IF _url IS NOT NULL
    THEN
        _urltypeid := reference.urltype(_url);
        IF _urltypeid = 1
        THEN
            RETURN
                (SELECT row(location, auxiliary, response) FROM tablea);
        END IF;
    IF _urltypeid = 2
        THEN
            RETURN
                (SELECT row(ip, location, host, authority) FROM tableb);
        END IF;
    END IF;
END;
$$;

Other composite types, such as jsonb and hstore are also a solution.

edited Jan 11 '21 at 18:19

answered Jan 11 '21 at 17:43

Jonathan Jacobson

1,441
11
20

1

The trouble with returning a `record` is that when you use it you need to declare the columns and types for the output. So you are back to the problem of not knowing what they are going to be when the function is run. – Adrian Klaver Jan 11 '21 at 17:46
@AdrianKlaver I don't agree with your claim. The OP requested a dynamic solution. Whether it requires a declaration of columns and types or not depends heavily on how the function output is being used, which we know nothing about. – Jonathan Jacobson Jan 11 '21 at 17:50
It is not an option, if you `RETURNS record` then you have to declare what it is being returned into on use. This is not the same as declaring a variable of `record` type inside `plpgsql`. – Adrian Klaver Jan 11 '21 at 18:04
Of course it's an option. It's 100% implementation dependent and it all depends on the language you're using and on what you're planning to do with the result. – Jonathan Jacobson Jan 11 '21 at 18:16
This returns error of "ERROR: cannot use RETURN QUERY in a non-SETOF function" – volume one Jan 11 '21 at 18:17
@volumeone As I said, haven't tested it. Drop the `QUERY` keyword. – Jonathan Jacobson Jan 11 '21 at 18:19
1

@JonathanJacobson. See my answer below for why this will not work the way you think. If you can generate a working/tested example that proves otherwise, please do so as I would be most interested. – Adrian Klaver Jan 11 '21 at 18:40

How to return result of dynamic SELECT inside a function in PostgreSQL?

2 Answers2