Column definition list requiered error when calling my function

Question

Im trying to build a function on hand from a query like this:

        Select * from dblink(
                            'host=db01
                            user=admin
                            password=xxxx
                            dbname=rm',
                            'select name,scopes from services es') as x(name varchar,scopes varchar)
    union all
        Select * from dblink(
                            'host=db02
                            user=admin
                            password=xxxx
                            dbname=rm',
                            'select name,scopes from services es') as x(name varchar,scopes varchar);

This Query works and now i want to create a reusable function to make other queries across this connections. Therefor i want to pass the select in one parameter and the alias with datatype in the second parameter.

This is how my Function looks like atm.

CREATE OR REPLACE FUNCTION custom.dblink_function(qr varchar, alias varchar)
 RETURNS TABLE(name character varying, scopes character varying)
 LANGUAGE plpgsql
AS $$

begin 
    

    
        return query 
        Select * from dblink(
                            'host=db01
                            user=admin
                            password=xxxx
                            dbname=rm',
                            qr) as x(alias)
    union all
        Select * from dblink(
                            'host=db02
                            user=admin
                            password=xxxx
                            dbname=rm',
                            qr) as x(alias);
                
            
end;
$$  

When i call it like this i get the following error.

select * from custom.dblink_function('''select name,scopes from services es''','name varchar,scopes varchar')  

**

SQL Error [42601]: ERROR: a column definition list is required for functions returning "record"
  Where: PL/pgSQL function custom.dblink_function(character varying,character varying) line 10 at RETURN QUERY

**

What am i doing wrong?

Answer 1

"alias" is a varchar, not a column definition list (which as far as I know has no type). It may contain text that can be interpreted as a column list, but for that to work you would need to assemble the query text together into a string, and then use return query execute to execute the string.

You would need something like

CREATE OR REPLACE FUNCTION dblink_function(qr varchar, alias varchar)
 RETURNS TABLE(name character varying, scopes character varying)
 LANGUAGE plpgsql
AS $$
begin 
        return query execute
        format ($R$Select * from dblink(
                            'host=db01
                            user=admin
                            password=xxxx
                            dbname=rm',
                            %L) as x(%s)
    union all
        Select * from dblink(
                            'host=db02
                            user=admin
                            password=xxxx
                            dbname=rm',
                            %L) as x(%s)$R$,qr,alias,qr,alias);end;
$$;