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$
Sanitize function
The manual has all the basics for PL/pgSQL. Basically, what you have can be simplified / sanitized to:
CREATE OR REPLACE FUNCTION func_a(username text = '', databaseobject text = '')
RETURNS ???? 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$ LANGUAGE plpgsql;
You don't need additional instances of BEGIN .. END
in the function body except to start a separate code block with its 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 you don't use them at all:
- Are PostgreSQL column names case-sensitive?
varchar(4000)
is also tailored to a specific limitation of SQL Server. This data type has no performance benefit whatsoever in Postgres. Only use it 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 I simplified 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 is a bit tricky, since SQL requires the function to return a well defined type. If you have a table or view or composite type in your database already that matches the column definition list you want to return you can just use that:
CREATE FUNCTION foo()
RETURNS SETOF my_view AS
...
If you are making the type up as you go, you can either return anonymous records:
CREATE FUNCTION foo()
RETURNS SETOF record AS
...
or provide a column definition list with (simplest) RETURNS TABLE
:
CREATE FUNCTION foo()
RETURNS TABLE (col1 int, col2 text, ...) AS
...
The downside for anonymous records: you then 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) AS
$func$
BEGIN
RETURN QUERY EXECUTE
format ('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, I'd rather use a plain SQL query to begin with. Not a function.
There are more advanced options, but you may need to study the basics first.
- Refactor a PL/pgSQL function to return the output of various SELECT queries