Postgresql equivalent of SQL Server query

Question

I am migrating database from SQL Server to Postgresql and I am currently stuck at converting this query. I have referred to the documentation but I don't seem to find any solution.

Here is the query (undeclared @variables are .NET variables):

declare @table_name as varchar(64) 
set @table_name = (select tbl_name from tbl1 where id = @id)

if @table_name = 'xyz' 
begin
    exec('select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and condition2='+@vbl2+' and condition3=''abc'' and condition4='''+@vbl4+'''
          union all
          select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and isnull(col3,''0'')=''0'' and condition3=''abc'' and condition4='''+@vbl4+'''
         ')
end
else
begin
    exec('select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and condition2='+@vbl2+' and condition3=''abc''
          union all
          select col1, col2, col3, col4 from '+@table_name+' where condition1='+@vbl1+' and isnull(col3,''0'')=''0'' and condition3=''abc''
         ')
end

I tried to convert this and here is what I could do:

create function ufn_function1(V_tbl_name anyelement, V_id integer, vbl1 integer, vbl2 integer, vbl4 integer) returns setof anyelement as $$
declare
V_table_name varchar:=(select V_tbl_name from tbl1 where id=V_id);
begin
if V_table_name= 'xyz'
then
return query execute format('
select col1, col2, col3, col4 from '||V_table_name||' where condition1='||vbl1||' and condition2='||vbl2||' and condition3=''abc'' and condition4='''||vbl4||'''
union all
select col1, col2, col3, col4 from '||V_table_name||' where condition1='||vbl1||' and isnull(col3,''0'')=''0'' and condition3=''abc'' and condition4='''||vbl4||'''
  ');
else
return query execute format(' 
select col1, col2, col3, col4 from '||V_table_name||' where condition1='||vbl1||' and condition2='||vbl2||' and condition3=''abc''
union all
select col1, col2, col3, col4 from '||V_table_name||' where condition1='||vbl1||' and isnull(col3,''0'')=''0'' and condition3=''abc''
 ');
end if;
end;
$$ language 'plpgsql';

Obviously this is not the correct conversion and has multiple syntax errors as I am completely alien to Postgresql.

Any help in converting this query is highly appreciated.

Answer 1

You really need to rethink your approach.

Generally, plain SQL does not allow interpolation / parameterization for identifiers (table or columns names etc.). You need to concatenate the query string, and then execute it. In PL/pgSQL, you can do this kind of "dynamic SQL" with EXECUTE. The function format() is instrumental in concatenating the string safely.

But the return type of a function cannot be completely dynamic. There are limited workarounds. Like returning anonymous records (which I don't find very useful). Or a polymorphic functions. See:

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

But that requires you to pass the return type with the call, which is not possible with your workflow, where you only pass an ID to fetch a table name. Plus, your original code seems inefficient and open to SQL injecton. However, your return type looks constant after all. If so, this could work:

CREATE OR REPLACE FUNCTION ufn_function1(_id int, _vbl1 int, _vbl2 int, _vbl4 int)
  -- OUT columns must match result!
  -- Falling back to text while undisclosed
  RETURNS TABLE (col1 text, col2 text, col3 text, col4 text)
  LANGUAGE plpgsql AS
$func$
DECLARE
   -- the cast to regclass raises an exception immediately
   -- if tbl1.tbl_name is not a valid table name (or null)
    _tbl regclass := (SELECT tbl_name::regclass FROM tbl1 WHERE id = _id);
BEGIN
   IF _table_name IS NULL THEN     -- no table name, no go
      RAISE WARNING 'No table name found!';

   ELSIF _table_name = 'xyz' THEN  -- constant table name, plain SQL
      RETURN QUERY
      -- casting result columns to text while actual types are undisclosed
      SELECT t.col1::text, t.col2::text, t.col3::text, t.col4::text  
      FROM   xyz t
      WHERE  t.condition1 = _vbl1
      AND    t.condition2 = _vbl2
      AND    t.condition4 = _vbl4
      AND   (t.condition3 = 'abc' OR t.col3 <> '0' IS NOT TRUE);

   ELSE   -- dynamic table name, dynamic SQL
      RETURN QUERY EXECUTE format(
         $q$
         SELECT t.col1::text, t.col2::text, t.col3::text, t.col4::text
         FROM   %I t
         WHERE  t.condition1 = $1
         AND    t.condition2 = $2
         AND    t.condition4 = $3
         AND   (t.condition3 = 'abc' OR t.col3 <> '0' IS NOT TRUE)
         $q$, _table_name)
      USING _vbl1  -- $1
          , _vbl2  -- $2
          , _vbl4  -- $3
      ;
   END IF;
END;
$func$;

There are subtle details to this. Too much for a single question.
Start by learning PL/pgSQL.

Related:

• Table name as a PostgreSQL function parameter
• Best way to check for "empty or null value"
• What is the PostgreSQL equivalent for ISNULL()