[SQL] Dynamic sql

[Gabriel Filipiak]

Hi, this is my first on this list.

I want to know the basics of dynamic sql especially in PostgreSQL. I was
googling for a while but have no luck for getting a good described examples.
Maybe someone here could give me some links to the materials from which you
where studying this subject.

Ok so to be more accurate I am interested in examples of especially PL/SQL
(PostgreSQL) code which will demonstrate examples with description :)

Best regards

Gabe

[SQL] Problem with pivot tables

[Gabriel Filipiak]

I have problem with creating a pivot table in postgreSQL using crosstab
function. It works well but it produces many records for the same
client_id, how can I avoid it?

Here is the SQL:

SELECT * FROM crosstab('SELECT client_id,extract(year from date),
sum(amount)  from orders group by extract(
year from date), client_id','SELECT extract(year from date) FROM orders
GROUP BY extract(year from date) order by extract(year from date)')
AS orders(
row_name integer,
year_2001 text,
year_2002 text,
year_2003 text,
year_2004 text,
year_2005 text,
year_2006 text,
year_2007 text,
year_2008 text,
year_2009 text,
year_2010 text,
year_2011 text);

best regard and thx in advance
Gabe

[SQL] Problem with tables and columns names

[Gabriel Filipiak]

Hi,

so I am working on PostgreSQL 9.1.2 on x86_64-unknown-linux-gnu, compiled
by gcc (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 64-bit.

It seems that i can't give a table name for example testTable it has to be
test_table, because I can't access it via psql is that right or am I doing
something wrong? The same thing for columns. Can anyone give me a hint
about that?

What is really confusing is that I can access those tables and columns via
SQLAlchemy.

Thx in advance
best regards
Gabe