plpgsql - Return SETOF rows from PostgreSQL function

Question

I have a situation where I want to return the join between two views. and that's a lot of columns. It was pretty easy in sql server. But in PostgreSQL when I do the join. I get the error "a column definition list is required".

Is there any way I can bypass this, I don't want to provide the definitions of returning columns.

CREATE OR REPLACE FUNCTION functionA(username character varying DEFAULT ''::character varying, databaseobject character varying DEFAULT ''::character varying)
  RETURNS SETOF ???? AS
$BODY$
Declare 
SqlString varchar(4000) = '';
BEGIN
IF(UserName = '*') THEN
   Begin
   SqlString  := 'select * from view1 left join ' + databaseobject  + ' as view2 on view1.id = view2.id';
   End;
ELSE
    Begin
    SqlString := 'select * from view3 left join ' + databaseobject  + ' as view2 on view3.id = view2.id';
    End;
END IF; 
execute (SqlString  );
END;
$BODY$

Answer 1

Sanitize function

What you currently have can be simplified / sanitized to:

CREATE OR REPLACE FUNCTION func_a (username text = '', databaseobject text = '')
  RETURNS ????
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format ('SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)'
         , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END
         , databaseobject);
END
$func$;

You only need additional instances of BEGIN ... END in the function body to start separate code blocks with their own scope, which is rarely needed.

The standard SQL concatenation operator is ||. + is a "creative" addition of your former vendor.

Don't use CaMeL-case identifiers unless you double-quote them. Best don't use them at all See:

• Are PostgreSQL column names case-sensitive?

varchar(4000) is also tailored to a specific limitation of SQL Server. It has no specific significance in Postgres. Only use varchar(4000) if you actually need a limit of 4000 characters. I would just use text - except that we don't need any variables at all here, after simplifying the function.

If you have not used format(), yet, consult the manual here.

Return type

Now, for your actual question: The return type for a dynamic query can be tricky since SQL requires that to be declared at call time at the latest. If you have a table or view or composite type in your database already matching the column definition list, you can just use that:

CREATE FUNCTION foo()
  RETURNS SETOF my_view AS
...

Else, spell the column definition list with out with (simplest) RETURNS TABLE:

CREATE FUNCTION foo()
  RETURNS TABLE (col1 int, col2 text, ...) AS
...

If you are making the row type up as you go, you can return anonymous records:

CREATE FUNCTION foo()
  RETURNS SETOF record AS
...

But then you have to provide a column definition list with every call, so I hardly ever use that.

I wouldn't use SELECT * to begin with. Use a definitive list of columns to return and declare your return type accordingly:

CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
  RETURNS TABLE(col1 int, col2 text, col3 date)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE
   format ($f$SELECT v1.col1, v1.col2, v2.col3
              FROM %s v1 LEFT JOIN %I v2 USING (id)$f$
         , CASE WHEN username = '*' THEN 'view1' ELSE 'view3' END
         , databaseobject);
END
$func$;

For completely dynamic queries, consider building the query in your client to begin with, instead of using a function.

You need to understand basics first:

• Refactor a PL/pgSQL function to return the output of various SELECT queries
• PL/pgSQL in the Postgres manual

Then there are more advanced options with polymorphic types, which allow you to pass the return type at call time. More in the last chapter of:

• Refactor a PL/pgSQL function to return the output of various SELECT queries