4

PostgreSQL implements the execute ... using option to pass parameters into dynamic SQL, and as far as I can tell this feature was introduced in version 8.4. We are using Greenplum, which is forked from PostgreSQL at version 8.2, so it does not have this feature.

Is there any other way to do the same thing in Greenplum or PostgreSQL 8.2?

Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
PhilHibbs
  • 539
  • 1
  • 7
  • 22

2 Answers2

5

I cannot tell for sure, but there is a hint in the CREATE FUNCTION documentation:

langname The name of the language that the function is implemented in. May be SQL, C, internal, or the name of a user-defined procedural language. See CREATE LANGUAGE for the procedural languages supported in Greenplum Database.

In turn, the referenced place says

The PL/pgSQL language is installed by default in Greenplum Database.

Now the EXECUTE ... USING is a PL/pgSQL feature, so the only question is which PostgreSQL version it comes from. The documentation points to 8.2 - and this way it looks like you are out of luck.

I've contacted the Greenplum support to get a definitive answer, but never got an answer.

As for working around the lack of that construct, you can obviously concatenate together any SQL string you want and execute it. Be careful to correctly quote the passed-in values and avoid SQL injection.

András Váczi
  • 31,778
  • 13
  • 102
  • 151
0

You can use a workaround by using a stored function mentioned by dezso : 

CREATE OR REPLACE FUNCTION dynamic_query (table_name TEXT) RETURNS VOID AS $$
BEGIN
    EXECUTE 'SELECT * FROM ' || table_name || ' WHERE column1 LIKE ''a_value''';
END;
$$ language plpgsql;
kelvien
  • 111
  • 4