Re: Dynamic sql real examples

On 13/10/11 17:55, Gabriel Filipiak wrote:
> Hi all,
>
> I have lately learned what is dynamic sql and one of the most
> interesting features of it to me is that we can use dynamic columns
> names and tables. But I cannot think about useful real life examples.
> The only one that came into my mind is statistical table.
>
> Let`s say that we have table with name, type and created_data. Then we
> want to have a table that in columns are years from created_data
> column and in row type and number of names created in years. (sorry
> for my English)
>
> What can be other useful real life examples of using dynamic sql with
> column and table as parameters? How do you use it?
>
>
> I am particulary interested in examples that do not contain
> administrative things or database convertion or something like that, I
> am looking for examples where the code in example java is more
> complicated than using a dynamic sql in for example stored procedure.
>
> Thanks for any suggestions and help :) regards Gabe
>
Hi Gabe,

I have experience both in the design and implementation of Java systems,
as well as in creating and querying databases (from Java and directly
using SQL).

A complex query will be executed by PostgreSQL far more efficiently than
a series of simpler queries -- even if both are initiated via JDBC.

An example where dynamic SQL would useful would be in SQL generated to
support a search function with multiple fields, some of which are
optional. From memory, when I did this in Java, the Java application
constructed the query and passed it via JDBC to the database.

In another situation, I constructed stored procedures in Sybase
TransactSQL with dynamically executed SQL to support a report generation
program where some of the search fields where optional. PostgreSQL is
easier to work with, but it was an existing database. Also using Java
was not practicable.

Be very careful to avoid SQL injection attacks. Consider using the
functions:

|quote_ident(string text)|

and

|quote_literal(string text)|

(see the section '9.4. String Functions and Operators' of the PostgreSQL
9.1.1 manual)

Cheers,
Gavin

.