Postgres Pro
Downloads
Home   >   mailing lists

Thread: Dynamic sql real examples

Dynamic sql real examples

From
Gabriel Filipiak
Date:
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



Re: Dynamic sql real examples

From
Gabriel Filipiak
Date:
Thx Gavin,

any other suggestions from others?

Gabe

2011/10/13 Gavin Flower <GavinFlower@archidevsys.co.nz>
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





.


Re: Dynamic sql real examples

From
Gabriel Filipiak
Date:


2011/10/13 Gavin Flower <GavinFlower@archidevsys.co.nz>
On 13/10/11 18:35, Gabriel Filipiak wrote:
Thx Gavin,

any other suggestions from others?

Gabe

2011/10/13 Gavin Flower <GavinFlower@archidevsys.co.nz>
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


Hi Gabe,

Please do not 'top post'.  In these mailings lists, you are expected to add your comments either interpersed, or (more normally) at the bottom.  This allows people to read the context, before they read your comments.


Cheers,
Gavin


Sorry about that.

Gabe

Re: Dynamic sql real examples

From
John R Pierce
Date:
>     Please do not 'top post'.  In these mailings lists, you are
>     expected to add your comments either interpersed, or (more
>     normally) at the bottom.  This allows people to read the context,
>     before they read your comments.
>
> Sorry about that.

the other half of not top posting is editting the quotes so you leave
out all the extraneous stuff but what you're actually replying to.


--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast

Re: Dynamic sql real examples

From
Pavel Stehule
Date:
Hello

It's really depends on client software and architecture. Dynamic SQL
is interesting for some use cases when you use a stored procedures,
when you dynamically create tables based on metadata and when you
access these tables.

We had a object oriented database with interface in stored procedures.
Some classes was stored with own tables, some classes was stored in
common tables. But a format was transparent for client.

Some-times you have to use a dynamic sql as workaround - there is no
other way - like this

http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql

Regards

Pavel Stehule

2011/10/13 Gabriel Filipiak <gabriel.filipiak@gmail.com>:
> 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
>
>

Re: Dynamic sql real examples

From
Gavin Flower
Date:
On 13/10/11 18:44, Gabriel Filipiak wrote:


2011/10/13 Gavin Flower <GavinFlower@archidevsys.co.nz>
On 13/10/11 18:35, Gabriel Filipiak wrote:
Thx Gavin,

any other suggestions from others?

Gabe

2011/10/13 Gavin Flower <GavinFlower@archidevsys.co.nz>
On 13/10/11 17:55, Gabriel Filipiak wrote:
[...]
Hi Gabe,

Please do not 'top post'.  In these mailings lists, you are expected to add your comments either interpersed, or (more normally) at the bottom.  This allows people to read the context, before they read your comments.


Cheers,
Gavin


Sorry about that.

Gabe

No worries, I still make silly mistakes like replying directly to people and forgetting to cc the list!

Also don't worry to much about your English! If you read some of my posts you will sometimes wonder if English is my frst language (it is), but sometimes my grammer and spelling leaves much to be desired - especially when I am tired, I'm in a hurry, or my eyesight is poor (the latter will hopefully be fixed soon).


Cheers,
Gavin

Re: Dynamic sql real examples

From
Gavin Flower
Date:
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





.