We have a function that executes dynamic SQL (I know, SQL injection, the "Bobby Tables" attack, but that's a separate issue). It takes two parameters, interpolates the first into the second, and returns a boolean.
When we execute it like this:
SELECT util.dynsql('M','SELECT EXISTS (SELECT 1 FROM x WHERE y = INPUT_VALUE)');
then it works fine. It puts the 'M' in where the INPUT_VALUE is, finds it in table x, and returns true.
If we execute it like this:
SELECT util.dynsql('M','SELECT EXISTS (SELECT 1 FROM x WHERE y = INPUT_VALUE)') FROM some_table limit 1;
then it returns false. some_table can be any table, the thing is that when there's a FROM, it doesn't work.
Any suggestions why? And no, sorry I can't post the code of the dynsql function.
If the second parameter does not contain a SELECT, if it's a comparison or regex, then it works. It seems that a SELECT in dynamic SQL doesn't work in a query from a table, but does work in a query that isn't from a table.
This is on Greenplum, which doesn't have parameterized dynamic SQL, that was introduced in PostgreSQL 8.4 and Greenplum is build on Postgres 8.2.