current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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$
share|improve this question

1 Answer 1

up vote 8 down vote accepted

Sanitize function

It's pretty obvious you come from an SQL Server background. You need to learn a bit of the local language - and unlearn some things you took for granted in SQL Server. Start by reading the manual.

Basically, what you have there, 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.

  • varchar(4000) is also tailored to a specific limitation of SQL Server. This data type has no benefit whatsoever in Postgres, unless you actually need an limit of 4000 characters. Just use text everywhere. However, you don't need any variables at all here, after I simplified the function.

Return type

Now, for your actual question: The return type for a dynamic query is a bit tricky, since SQL requires a 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 a anonymous record

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

or provide a column definition list with (simplest) RETURN 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.