Greenplum – Does It Support Dynamic SQL?

dynamic-sqlgreenplum

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?

Best Answer

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.

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

One way to get this done is probably something you have already done, and that is to replace your line:

if @DebugMode=1 print @SQL

with

if @DebugMode=1 print @SQL + ' ' + convert(nvarchar(max), @Foobar)

And you would have to do it this way for all your variables, you will need to convert them manually to avoid conversion errors.

You could also use RAISERROR in a similar fashion:

if @DebugMode=1 RAISERROR (N'We used a value of %d for @Foobar', 10, 1, @Foobar)

HTH

Sql-server – How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receive

Because RECEIVE is basically a DELETE and as such has a query plan, it must obey the same restrictions SELECT/INSERT/DELETE/UPDATE statements have, specifically the restrictions that the object it acts on must be known at compile time, not at execution time.

The only option is to use dynamic SQL, with all the blessings and pitfalls that follow.

You could also generate the procedure body during project deployment, have a single template and generate a specific procedure for each queue, specialized for the specific queue name. Whether this is feasible depends on many factors, first and foremost on the organization of your project and how you deploy.

On a side note I'm surprised to hear that you have many queues. In general the tendency is to have a single queue and several queue readers (activated procedures). Since SSB programing is event driven (wait for for message, process message, wait for message, process message, wait for message...) having more than one queue to wait for messages on it becomes harder, as the application now has to wait on multiple sources (eg. one thread per queue at least). Even with SSB activation, which alleviates the need for explicit wait wince it launches the code to process the message on-demand, multiple queues are harder to manage (max_queue_readers per queue adds up to perhaps too many internal activated procedures launched). Consider using a single service and queue on the RECEIVE side. Even when multiple services are needed (for whatever reason) they can be consolidated on a single queue.

Best Answer

Related Solutions

Sql-server – Print Parameters in Dynamic SQL

Sql-server – How to make a flexible service broker receive in a stored sql procedure, how to pass “FROM” parameter into receive

Related Question