Seamless dynamic SQL using PostgreSQL

Have you seen seamless dynamic query on a SQL Server function?

Neither do I, so I will show to you a PostgreSQL approach.

To cut to the chase, here's the query:

create or replace function DynamicSql(p_fields varchar)
    returns table
    (    
        "YouKnowMyName" varchar,
        "LookupTheNumber" int
    ) as    
$$
declare
    sql varchar;    
begin

    sql = 'select ' || p_fields || ' from employees';
    
    return query execute sql;
end;
$$ language 'plpgsql';

select * from DynamicSql(' "LastName", "EmployeeID" ' );
select * from DynamicSql(' "FirstName", "EmployeeID" ' );

Look Ma! No Drama!



Contrast that on how it is done on other database *cough* Sql Server Table-Valued Function via CLR *cough*:

http://www.ienablemuch.com/2013/07/streaming-table-valued-function-via-clr.html

Accomplishing dynamic SQL on a Table-Valued Function via CLR is too ceremonial and has too much drama, y'know you gotta need to accomplish all of these: FillRowMethodName, Streaming Table-Valued Functions via yield return, assembly signing, sp_configure 'clr enabled', create asymmetric key, grant external access, blah blah and all that shiznit.


Microsoft should be included on the list of featured PostgreSQL users given that Microsoft bought Skype. I Love Technology!


"You already trust PostgreSQL, you just may not know it yet..." -- http://www.2ndquadrant.com/en/who-uses-postgresql/

Happy Coding! ツ