date:20101228

Re: [GENERAL] CONSTRAINT does not show when applying a EXCLUDE constraint

2010-12-28 Thread InterRob

Dear Tom,

Thanks for your hints; it is indeed funny (as mentioned by Guillaume) how
indexes and constraints are being mixed up -- mentioned either in the
Indexes or in Constraints department. Pgsql and PgAdmin make different
choices here. And well, given their implementation these choices can both be
understood...

Anyway, your hint using UNIQUE is what I thought of myself earlier as well.
Yet, I don't get it to work:
CREATE TABLE unique_test(subject_id INTEGER, object_id INTEGER,
UNIQUE(imm_least(subject_id, object_id), imm_greatest(subject_id,
object_id)));

The above command generates a syntax error at the postion of the first
parenthesis of the first function call inside the UNIQUE expression... From
the PG syntax reference I understand only column expressions are allowed...
It seems to me that CASE is not supported either.

Am I missing something?


Rob

2010/12/27 Tom Lane t...@sss.pgh.pa.us

 Richard Broersma richard.broer...@gmail.com writes:
  On Mon, Dec 27, 2010 at 12:50 PM, InterRob rob.mar...@gmail.com wrote:
  pgsql Command \d test produces the following:
 Table public.test
 Column   |  Type   | Modifiers
  +-+---
   object_id  | integer |
   subject_id | integer |
  Indexes:
  EXCL_double_combi EXCLUDE USING btree (imm_least(subject_id,
  object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)

  I see.  Since exclusion constraints is a rather new feature, psql may
  need some tweaking to report these constraints as constraints

 No, the behavior is entirely intentional.  Compare what it's always been
 for primary keys:

 regression=# create table foo (f1 int primary key);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey
 for table foo
 CREATE TABLE
 regression=# \d foo
  Table public.foo
  Column |  Type   | Modifiers
 +-+---
  f1 | integer | not null
 Indexes:
foo_pkey PRIMARY KEY, btree (f1)

 regression=#

 BTW, I fail to see the point of using EXCLUDE in this particular way.
 You'd get the same results, more efficiently, with a plain UNIQUE
 constraint on the two function expressions.  EXCLUDE is really only
 interesting for cases where the behavior you want doesn't conform to
 btree semantics.

regards, tom lane

Re: [GENERAL] Startup Process Initiated by init proc (Unix)

2010-12-28 Thread aaliya zarrin

I can tell the changes I have done in the code if you want.

On Tue, Dec 28, 2010 at 12:30 PM, aaliya zarrin aaliya.zar...@gmail.comwrote:

 Sorry, My application is using the postgres DB but the another application
 is giving the command to run in standby or active mode.
 I can't use gdb I beleive.
 Can you suggest any other way.

   On Tue, Dec 28, 2010 at 12:18 PM, Fujii Masao masao.fu...@gmail.comwrote:

 On Tue, Dec 28, 2010 at 3:07 PM, aaliya zarrin aaliya.zar...@gmail.com
 wrote:
  I didn't get? Do you want the syslog files or some more information?

 Yeah, I want more information. You can get the backtrace by using gdb.
 In detail, please see the following:

 http://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD

 Regards,

 --
 Fujii Masao
 NIPPON TELEGRAPH AND TELEPHONE CORPORATION
 NTT Open Source Software Center




  --
 Thanks  Regards,

 Aaliya Zarrin
 (+91)-9160665888




-- 
Thanks  Regards,

Aaliya Zarrin
(+91)-9160665888

[GENERAL] PowerDesigner 15

2010-12-28 Thread el dorado


Hello!

I would like to get more flexible tool for database management that EMS of 
pgadmin. So now I'm trying to use PD 15 with Postgres 8.4.1. It's very exciting 
:)
So what can I say?
It seems to be there are no problems with tables, sequences, FK... But there 
are some difficulties with:
1. Stored porcedures
At this branch of DBMS definition file  - PostgreSQL 
8::Script\Objects\Procedure\Create - I write the following:

CREATE FUNCTION %PROC%(%PROCPRMS%)
as $body$
%TRGDEFN%
$body$
LANGUAGE  '%ProcLanguage%'  VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

When I do reverse engineering I get the script of procedure, f.e.:
CREATE FUNCTION %PROC%()
as $body$
BEGIN   
RETURN i+1; 
END
$body$
LANGUAGE  '%ProcLanguage%'  VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

The problem is that there is that there should be some params - but here the 
list of params is empty. 

2. Schemas
PD interprets schemas as users - for some reason. I have no definite idea - is 
there any opportunity to teach PD to work with schemas correctly? Maybe it's 
necessary to add some structure to 'Object' section or 'Profile' section of 
DBMS definition file ?

It's interesting at all - does anybody successfully use PD with Postgres? I'll 
appreciate any notices. There is no much information about it.

Regards, Marina.

Re: [GENERAL] CONSTRAINT does not show when applying a EXCLUDE constraint

2010-12-28 Thread InterRob

SOLVED: I should use the CREATE UNIQUE INDEX ... ON ... command, on an
existing table. This cannot be done inline within the CREATE TABLE command.

Thanks all, for your help.


Rob

2010/12/28 InterRob rob.mar...@gmail.com

 Dear Tom,

 Thanks for your hints; it is indeed funny (as mentioned by Guillaume) how
 indexes and constraints are being mixed up -- mentioned either in the
 Indexes or in Constraints department. Pgsql and PgAdmin make different
 choices here. And well, given their implementation these choices can both be
 understood...

 Anyway, your hint using UNIQUE is what I thought of myself earlier as well.
 Yet, I don't get it to work:
 CREATE TABLE unique_test(subject_id INTEGER, object_id INTEGER,
 UNIQUE(imm_least(subject_id, object_id), imm_greatest(subject_id,
 object_id)));

 The above command generates a syntax error at the postion of the first
 parenthesis of the first function call inside the UNIQUE expression... From
 the PG syntax reference I understand only column expressions are allowed...
 It seems to me that CASE is not supported either.

 Am I missing something?


 Rob

 2010/12/27 Tom Lane t...@sss.pgh.pa.us

 Richard Broersma richard.broer...@gmail.com writes:
  On Mon, Dec 27, 2010 at 12:50 PM, InterRob rob.mar...@gmail.com
 wrote:
  pgsql Command \d test produces the following:
 Table public.test
 Column   |  Type   | Modifiers
  +-+---
   object_id  | integer |
   subject_id | integer |
  Indexes:
  EXCL_double_combi EXCLUDE USING btree (imm_least(subject_id,
  object_id) WITH =, imm_greatest(subject_id, object_id) WITH =)

  I see.  Since exclusion constraints is a rather new feature, psql may
  need some tweaking to report these constraints as constraints

 No, the behavior is entirely intentional.  Compare what it's always been
 for primary keys:

 regression=# create table foo (f1 int primary key);
 NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey
 for table foo
 CREATE TABLE
 regression=# \d foo
  Table public.foo
  Column |  Type   | Modifiers
 +-+---
  f1 | integer | not null
 Indexes:
foo_pkey PRIMARY KEY, btree (f1)

 regression=#

 BTW, I fail to see the point of using EXCLUDE in this particular way.
 You'd get the same results, more efficiently, with a plain UNIQUE
 constraint on the two function expressions.  EXCLUDE is really only
 interesting for cases where the behavior you want doesn't conform to
 btree semantics.

regards, tom lane

Re: [GENERAL] Startup Process Initiated by init proc (Unix)

2010-12-28 Thread aaliya zarrin

Hi,
I am unable to run the postgres using gdb.
I am getting error as  no debugging symbols found.
How to proceed?

On Tue, Dec 28, 2010 at 11:32 AM, Fujii Masao masao.fu...@gmail.com wrote:

 On Tue, Dec 28, 2010 at 2:24 PM, aaliya zarrin aaliya.zar...@gmail.com
 wrote:
  I am new to postgres. I am using 9.0.1 of postgres.
  I am using Switch over functionality of Postgres and want to reduce the
  switch over time.
  I have implemented this using Signaling. After signaling the postmaster
  (startup process) once the trigger file is created and making PG to check
  for trigger file
  and do the switch over. PG is receiving the signal and doing the switch
 over
  as well but two startup processes are running on standby node one
 initiated
  by postmested and other by init (unix process).
 
  The steps I am following are as follows,
   - search for trigger file.
  - Complete the recovery- rename recovery.conf file.
  any step is missing I am not sure.

 The backtrace of that orphan process would be helpful for the analysis.

 Regards,

 --
 Fujii Masao
 NIPPON TELEGRAPH AND TELEPHONE CORPORATION
 NTT Open Source Software Center




-- 
Thanks  Regards,

Aaliya Zarrin
(+91)-9160665888

Re: [GENERAL] PowerDesigner 15

2010-12-28 Thread Dmitriy Igrishin

28 декабря 2010 г. 13:02 пользователь el dorado do_ra...@mail.ru написал:

Hello!

I would like to get more flexible tool for database management that EMS of
pgadmin. So now I'm trying to use PD 15 with Postgres 8.4.1. It's very
exciting :)
So what can I say?
It seems to be there are no problems with tables, sequences, FK... But
there are some difficulties with:
1. Stored porcedures
At this branch of DBMS definition file - PostgreSQL
8::Script\Objects\Procedure\Create - I write the following:

CREATE FUNCTION %PROC%(%PROCPRMS%)
as $body$
%TRGDEFN%
$body$
LANGUAGE '%ProcLanguage%' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

When I do reverse engineering I get the script of procedure, f.e.:
CREATE FUNCTION %PROC%()
as $body$
BEGIN
RETURN i+1;
END
$body$
LANGUAGE '%ProcLanguage%' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

The problem is that there is that there should be some params - but here
the list of params is empty.

2. Schemas
PD interprets schemas as users - for some reason. I have no definite idea -
is there any opportunity to teach PD to work with schemas correctly? Maybe
it's necessary to add some structure to 'Object' section or 'Profile'
section of DBMS definition file ?

It's interesting at all - does anybody successfully use PD with Postgres?
I'll appreciate any notices. There is no much information about it.

Regards, Marina.

Hey Marina,

We considered the possibility of usage PD with Postgres, but find another
tool -
dbWrench which we liked more.

--
// Dmitriy.

[GENERAL] DATA Location

2010-12-28 Thread Ozz Nixon

Is it possible (and how) to implement a data path on another partition (linux) 
for an existing system? And then if I do not gain anything, merging it to the 
production /data path?

Scenario of what I want to achieve (/mnt/data is already running)

/mnt/data   resides on an NFS share
Contains over 2 Billion web sites crawled (yeah 
another search-engine site)

want to add:

/opt/data   resides on internal drive
Will contain keyword hash system

Then if I find this does not improve anything - or runs tight (running on IBM 
Blade center with 76gb internals - so I may be limited), that I can simple 
shutdown postgres, scp /opt/data/folder/ to the NFS - bring up postgres - fix 
any conf file, and everything is on the multi-terabyte array?

Checking the pulse of all you speed freaks that just hit the floor because this 
is on NFS not iSCSI -- don't worry, when this site goes live it will be iSCSI. 
We have been in private development for 3 years... and the NFS still out runs 
the Internet connections.
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DATA Location

2010-12-28 Thread Raymond O'Donnell


On 28/12/2010 14:56, Ozz Nixon wrote:

Is it possible (and how) to implement a data path on another
partition (linux) for an existing system? And then if I do not gain
anything, merging it to the production /data path?


I think tablespaces will do what you want:

  http://www.postgresql.org/docs/9.0/static/manage-ag-tablespaces.html

HTH,

Ray.


--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DATA Location

2010-12-28 Thread Andreas Kretschmer

Ozz Nixon ozzni...@gmail.com wrote:

 Is it possible (and how) to implement a data path on another partition
 (linux) for an existing system? And then if I do not gain anything,
 merging it to the production /data path?

I think, you should read our docu about tablespaces:

http://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Alexander Farber

Hello,

I'm working on a small app, which receives a list of 20 players in XML format.

The initial version works ok and I use there just 1 SQL statement and thus
it is easy for me to fetch results row by row and print XML at the same time:

select u.id,
u.first_name,
u.city,
u.avatar,
m.money,
u.login  u.logout as online
 from pref_users u, pref_money m where

m.yw=to_char(current_timestamp, '-IW') and
u.id=m.id
 order by m.money desc
 limit 20 offset ?

My problem is however, that I need to add more data for each user
representing their statistics over the last 20 weeks.
And that data is in separate tables: pref_money, pref_pass, pref_game:

# select yw, money
from pref_money where id='OK122471020773'
order by yw desc limit 20;
   yw| money
-+---
 2010-52 |   760
 2010-51 |  3848
 2010-50 |  4238
 2010-49 |  2494
 2010-48 |   936
 2010-47 |  3453
 2010-46 |  3923
 2010-45 |  1110
 2010-44 |   185
(9 rows)

For example for the table above I'd like to concatenate
those rows and add them as an XML attribute for that user:

user id=OK122471020773 first_name=... city=... ...
pref_money=2010-52:760;2010-51:3848;2010-50:4238; /

so that I can take that attribute in my app and use it in a chart.

My problem is that I don't know how to bring this together
in 1 SQL statement (i.e. the SQL statement at the top and
then the concatenated 20 rows from 3 tables).

Is it possible? Maybe I need to write a PgPlSQL
procedure for each of the 3 tables and then add them
to the SQL statement above? But how do I concatenate
the rows, should I create a PgPlSQL variable and always
append values to it in a loop or is there a better way?

Thank you for any hints
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] DATA Location

2010-12-28 Thread Scott Marlowe

On Tue, Dec 28, 2010 at 7:56 AM, Ozz Nixon ozzni...@gmail.com wrote:
 Is it possible (and how) to implement a data path on another partition 
 (linux) for an existing system? And then if I do not gain anything, merging 
 it to the production /data path?

 Scenario of what I want to achieve (/mnt/data is already running)

        /mnt/data       resides on an NFS share
                                Contains over 2 Billion web sites crawled 
 (yeah another search-engine site)

 want to add:

        /opt/data               resides on internal drive
                                Will contain keyword hash system

 Then if I find this does not improve anything - or runs tight (running on IBM 
 Blade center with 76gb internals - so I may be limited), that I can simple 
 shutdown postgres, scp /opt/data/folder/ to the NFS - bring up postgres - fix 
 any conf file, and everything is on the multi-terabyte array?

You can't just merge two pgsql databases into one directory.  You have
to pick one to run and dump the other and load it into the other db
with pg_dump / psql / pg_restore.  If you completely replace the db
instance in /opt/data/folder with the other one that will work, but
erase the original db that was there.

 Checking the pulse of all you speed freaks that just hit the floor because 
 this is on NFS not iSCSI -- don't worry, when this site goes live it will be 
 iSCSI. We have been in private development for 3 years... and the NFS still 
 out runs the Internet connections.

It's more an issue of reliability in case of crashes and power losses
than anything else.  most iSCSI implementations will honor fsync
properly, but many nfs mounts will lose data / corrupt your data store
if one or the other machine crashes in the wrong way.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Devart ADO.NET Data Providers Deliver Advanced Model-First and Database-First Support and Improved ORM Solution!

2010-12-28 Thread Devart


New versions of Devart ADO.NET Data Providers with improved capabilities of
ORM solution and advanced Model-First and Database-First support in ORM
model designer.

Devart has recently announced the release of the new versions of dotConnect
Data Providers, the database connectivity solutions built over ADO.NET
architecture and development frameworks with a number of innovative
technologies. Devart ADO.NET Data Providers product line includes high
performance data providers for major databases and offer a complete solution
for developing database-related applications and web sites. Devart
dotConnect provides support for ADO.NET Entity Framework and LinqConnect.
LinqConnect is the Devart ORM solution, closely compatible with the
Microsoft LINQ to SQL, while extending its functionality with its own
features.

With new versions of dotConnect ADO.NET Data providers - dotConnect for
Oracle 6.00, dotConnect for MySQL 6.00, dotConnect for PostgreSQL 5.00, and
dotConnect for SQLite 3.00 – Devart continues to introduce innovative
approaches for ORM model designing and considerably improves the runtime of
LinqConnect. 
1. New versions of dotConnects contains new greatly improved ORM solution - 
LinqConnect 
The functionality of LinqConnect ORM was extended by the following:  
 Now LinqConnect supports Batch Update for DML statements execution on
SubmitChanges call, which improves performance of update operations.
 New kind of inheritance hierarchy was supported. In addition to Table Per
Hierarchy support Table per Type is supported now.
 LinqConnect package now includes the new templates for ASP.NET Dynamic Data
projects, that allows creation ASP.NET Dynamic Data applications using
LinqConnect.
 Upgraded LinqConnect ORM supports Parallel LINQ, which offers performance
improvements for your applications, using LinqConnect.
 Visual Studio Debugger Visualizer was added to LinqConnect to improve
readability of SQL code, generated for your LINQ queries, while debugging
your application.
2. Enhanced own ORM model designer - Entity Developer 
Entity Developer, that is containing in dotConnect Data Providers, allows
developing LINQ to SQL and Entity Framework models based on different
database engines. Now it provides advanced Model First support and Update
model from database functionality:
Model-First approach implementation in Entity Developer offers automatic
synchronization of mapping and storage part with the conceptual part of the
model and easy to use wizards for synchronizing database with the model. 
Synchronizing Database with the Model 
Entity Developer offers you two options for creating database based on your
model. You may either generate a create script, that creates a new database,
or use Update to Database Wizard that synchronizes an existing database with
the model. This wizard makes only necessary changes to the database and
retain the data if possible. Even changes inside entity are detected and
resulted in ALTER statements, the table doesn't need to be dropped and
created again. The wizard displays the tree of the changed objects and their
changes, allowing you to choose which database objects need to be
synchronized with the model, specify the renamed objects, etc. 
Mapping Synchronization 
When automatic mapping synchronization is enabled, changes to the conceptual
model are automatically applied to its mapping and storage part. For
example, when you add a class to the conceptual part, a corresponding table
is created in the storage part, and the class is automatically mapped to
this table. Even the most complex conceptual part changes are supported -
complex types, many-to-many associations, complex hierarchies. All these
changes can be automatically reflected in the storage part. However you may
make manual changes to the storage part, and they will be preserved when
editing conceptual part. 
Update From Database Wizard allows synchronizing your model with the
database for Entity Framework and LinqConnect in a fast and convenient way.
Unlike standard Visual Studio Update Wizard, Entity Developer Update From
Database Wizard tries to preserve manual changes to the model where
possible. The wizard detects all the database changes that can affect the
model, e.g. created and deleted tables and views, their columns and foreign
keys, column datatype changes, created and deleted stored procedures and
functions, changes to their parameters etc. 

Pricing and Availability
We offer a free Express edition for each product from the dotConnect product
line.

A Single license price starts from as little as $99.95, and you can always
choose the edition that matches your needs best. 

To learn more, download trial and free editions or order a license please
visit Devart site:
http://www.devart.com/dotconnect/ www.devart.com/dotconnect/ 

About Devart
Devart is a software development company with 11 years of experience on the
software market and over 20 thousands of devoted users. 
We specialize in providing

Re: [GENERAL] Devart ADO.NET Data Providers Deliver Advanced Model-First and Database-First Support and Improved ORM Solution!

2010-12-28 Thread Gurjeet Singh

I think this is not the right forum for such announcements.

Consider http://archives.postgresql.org/pgsql-announce/ . I think Postgres
weekly news will pick it up automatically from there.

Regards,

On Tue, Dec 28, 2010 at 8:45 AM, Devart sub...@devart.com wrote:


 New versions of Devart ADO.NET Data Providers with improved capabilities
 of
 ORM solution and advanced Model-First and Database-First support in ORM
 model designer.

 Devart has recently announced the release of the new versions of dotConnect
 Data Providers, the database connectivity solutions built over ADO.NET
 architecture and development frameworks with a number of innovative
 technologies. Devart ADO.NET Data Providers product line includes high
 performance data providers for major databases and offer a complete
 solution
 for developing database-related applications and web sites. Devart
 dotConnect provides support for ADO.NET Entity Framework and LinqConnect.
 LinqConnect is the Devart ORM solution, closely compatible with the
 Microsoft LINQ to SQL, while extending its functionality with its own
 features.

 With new versions of dotConnect ADO.NET Data providers - dotConnect for
 Oracle 6.00, dotConnect for MySQL 6.00, dotConnect for PostgreSQL 5.00, and
 dotConnect for SQLite 3.00 – Devart continues to introduce innovative
 approaches for ORM model designing and considerably improves the runtime of
 LinqConnect.
 1. New versions of dotConnects contains new greatly improved ORM solution -
 LinqConnect
 The functionality of LinqConnect ORM was extended by the following:
  Now LinqConnect supports Batch Update for DML statements execution on
 SubmitChanges call, which improves performance of update operations.
  New kind of inheritance hierarchy was supported. In addition to Table Per
 Hierarchy support Table per Type is supported now.
  LinqConnect package now includes the new templates for ASP.NET Dynamic
 Data
 projects, that allows creation ASP.NET Dynamic Data applications using
 LinqConnect.
  Upgraded LinqConnect ORM supports Parallel LINQ, which offers performance
 improvements for your applications, using LinqConnect.
  Visual Studio Debugger Visualizer was added to LinqConnect to improve
 readability of SQL code, generated for your LINQ queries, while debugging
 your application.
 2. Enhanced own ORM model designer - Entity Developer
 Entity Developer, that is containing in dotConnect Data Providers, allows
 developing LINQ to SQL and Entity Framework models based on different
 database engines. Now it provides advanced Model First support and Update
 model from database functionality:
 Model-First approach implementation in Entity Developer offers automatic
 synchronization of mapping and storage part with the conceptual part of the
 model and easy to use wizards for synchronizing database with the model.
 Synchronizing Database with the Model
 Entity Developer offers you two options for creating database based on your
 model. You may either generate a create script, that creates a new
 database,
 or use Update to Database Wizard that synchronizes an existing database
 with
 the model. This wizard makes only necessary changes to the database and
 retain the data if possible. Even changes inside entity are detected and
 resulted in ALTER statements, the table doesn't need to be dropped and
 created again. The wizard displays the tree of the changed objects and
 their
 changes, allowing you to choose which database objects need to be
 synchronized with the model, specify the renamed objects, etc.
 Mapping Synchronization
 When automatic mapping synchronization is enabled, changes to the
 conceptual
 model are automatically applied to its mapping and storage part. For
 example, when you add a class to the conceptual part, a corresponding table
 is created in the storage part, and the class is automatically mapped to
 this table. Even the most complex conceptual part changes are supported -
 complex types, many-to-many associations, complex hierarchies. All these
 changes can be automatically reflected in the storage part. However you may
 make manual changes to the storage part, and they will be preserved when
 editing conceptual part.
 Update From Database Wizard allows synchronizing your model with the
 database for Entity Framework and LinqConnect in a fast and convenient way.
 Unlike standard Visual Studio Update Wizard, Entity Developer Update From
 Database Wizard tries to preserve manual changes to the model where
 possible. The wizard detects all the database changes that can affect the
 model, e.g. created and deleted tables and views, their columns and foreign
 keys, column datatype changes, created and deleted stored procedures and
 functions, changes to their parameters etc.

 Pricing and Availability
 We offer a free Express edition for each product from the dotConnect
 product
 line.

 A Single license price starts from as little as $99.95, and you can always
 choose the edition that matches your needs best.

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Alexander Farber

I'm trying:

create or replace function pref_money_stats(_id varchar)
returns varchar as $BODY$
begin

declare stats varchar;

for row in select yw, money from pref_money where id=_id order
by yw desc limit 20 loop
stats := stats || ; || row.id || : || row.money;
end loop;

return stats;
end;
$BODY$ language plpgsql;

but get the error:

ERROR:  syntax error at or near for
LINE 7: for row in select yw, money from pref_money where id...
^

Regards
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Gary Chambers


Alex,


create or replace function pref_money_stats(_id varchar)
   returns varchar as $BODY$
   begin

   declare stats varchar;

   for row in select yw, money from pref_money where id=_id order
by yw desc limit 20 loop
   stats := stats || ; || row.id || : || row.money;
   end loop;

   return stats;
   end;
$BODY$ language plpgsql;

but get the error:

ERROR:  syntax error at or near for
LINE 7: for row in select yw, money from pref_money where id...


Your declare statement should be before the begin statement.

create or replace function pref_money_stats(_id varchar) returns varchar as
$$
declare
stats varchar;

begin
for row ...
return stats;
end;
$$ language plpgsql;

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Dmitriy Igrishin

2010/12/28 Alexander Farber alexander.far...@gmail.com

 Hello,

 I'm working on a small app, which receives a list of 20 players in XML
 format.

 The initial version works ok and I use there just 1 SQL statement and thus
 it is easy for me to fetch results row by row and print XML at the same
 time:

select u.id,
u.first_name,
u.city,
u.avatar,
m.money,
u.login  u.logout as online
 from pref_users u, pref_money m where

 m.yw=to_char(current_timestamp, '-IW') and
u.id=m.id
 order by m.money desc
 limit 20 offset ?

 My problem is however, that I need to add more data for each user
 representing their statistics over the last 20 weeks.
 And that data is in separate tables: pref_money, pref_pass, pref_game:

 # select yw, money
 from pref_money where id='OK122471020773'
 order by yw desc limit 20;
   yw| money
 -+---
  2010-52 |   760
  2010-51 |  3848
  2010-50 |  4238
  2010-49 |  2494
  2010-48 |   936
  2010-47 |  3453
  2010-46 |  3923
  2010-45 |  1110
  2010-44 |   185
 (9 rows)

SELECT string_agg(yw::text || money::text, ';');


 For example for the table above I'd like to concatenate
 those rows and add them as an XML attribute for that user:

 user id=OK122471020773 first_name=... city=... ...
pref_money=2010-52:760;2010-51:3848;2010-50:4238; /

 so that I can take that attribute in my app and use it in a chart.

 My problem is that I don't know how to bring this together
 in 1 SQL statement (i.e. the SQL statement at the top and
 then the concatenated 20 rows from 3 tables).

 Is it possible? Maybe I need to write a PgPlSQL
 procedure for each of the 3 tables and then add them
 to the SQL statement above? But how do I concatenate
 the rows, should I create a PgPlSQL variable and always
 append values to it in a loop or is there a better way?

 Thank you for any hints
 Alex

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
// Dmitriy.

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Dmitriy Igrishin

2010/12/28 Dmitriy Igrishin dmit...@gmail.com



 2010/12/28 Alexander Farber alexander.far...@gmail.com

 Hello,

 I'm working on a small app, which receives a list of 20 players in XML
 format.

 The initial version works ok and I use there just 1 SQL statement and thus
 it is easy for me to fetch results row by row and print XML at the same
 time:

select u.id,
u.first_name,
u.city,
u.avatar,
m.money,
u.login  u.logout as online
 from pref_users u, pref_money m where

 m.yw=to_char(current_timestamp, '-IW') and
u.id=m.id
 order by m.money desc
 limit 20 offset ?

 My problem is however, that I need to add more data for each user
 representing their statistics over the last 20 weeks.
 And that data is in separate tables: pref_money, pref_pass, pref_game:

 # select yw, money
 from pref_money where id='OK122471020773'
 order by yw desc limit 20;
   yw| money
 -+---
  2010-52 |   760
  2010-51 |  3848
  2010-50 |  4238
  2010-49 |  2494
  2010-48 |   936
  2010-47 |  3453
  2010-46 |  3923
  2010-45 |  1110
  2010-44 |   185
 (9 rows)

 SELECT string_agg(yw::text || money::text, ';');

Sorry,
SELECT string_agg(yw::text || ':' || money::text, ';');


 For example for the table above I'd like to concatenate
 those rows and add them as an XML attribute for that user:

 user id=OK122471020773 first_name=... city=... ...
pref_money=2010-52:760;2010-51:3848;2010-50:4238; /

 so that I can take that attribute in my app and use it in a chart.

 My problem is that I don't know how to bring this together
 in 1 SQL statement (i.e. the SQL statement at the top and
 then the concatenated 20 rows from 3 tables).

 Is it possible? Maybe I need to write a PgPlSQL
 procedure for each of the 3 tables and then add them
 to the SQL statement above? But how do I concatenate
 the rows, should I create a PgPlSQL variable and always
 append values to it in a loop or is there a better way?

 Thank you for any hints
 Alex

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




 --
 // Dmitriy.





-- 
// Dmitriy.

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Igor Neyman

 -Original Message-
 From: Alexander Farber [mailto:alexander.far...@gmail.com] 
 Sent: Tuesday, December 28, 2010 10:33 AM
 To: pgsql-general@postgresql.org
 Subject: Concatenating several rows with a semicolon

 Hello,

 I'm working on a small app, which receives a list of 20 
 players in XML format.

 The initial version works ok and I use there just 1 SQL 
 statement and thus it is easy for me to fetch results row by 
 row and print XML at the same time:

 select u.id,
 u.first_name,
 u.city,
 u.avatar,
 m.money,
 u.login  
 u.logout as online
  from pref_users u, 
 pref_money m where

 m.yw=to_char(current_timestamp, '-IW') and
 u.id=m.id
  order by m.money desc
  limit 20 offset ?

 My problem is however, that I need to add more data for each 
 user representing their statistics over the last 20 weeks.
 And that data is in separate tables: pref_money, pref_pass, pref_game:

 # select yw, money
 from pref_money where id='OK122471020773'
 order by yw desc limit 20;
yw| money
 -+---
  2010-52 |   760
  2010-51 |  3848
  2010-50 |  4238
  2010-49 |  2494
  2010-48 |   936
  2010-47 |  3453
  2010-46 |  3923
  2010-45 |  1110
  2010-44 |   185
 (9 rows)

 For example for the table above I'd like to concatenate those 
 rows and add them as an XML attribute for that user:

 user id=OK122471020773 first_name=... city=... ...
 pref_money=2010-52:760;2010-51:3848;2010-50:4238; /

 so that I can take that attribute in my app and use it in a chart.

 My problem is that I don't know how to bring this together in 
 1 SQL statement (i.e. the SQL statement at the top and then 
 the concatenated 20 rows from 3 tables).

 Is it possible? Maybe I need to write a PgPlSQL procedure for 
 each of the 3 tables and then add them to the SQL statement 
 above? But how do I concatenate the rows, should I create a 
 PgPlSQL variable and always append values to it in a loop or 
 is there a better way?

 Thank you for any hints
 Alex

Based on your PG version there are different solutions to your problem.
Not to re-invent the wheel, check this article:
http://www.postgresonline.com/journal/archives/191-String-Aggregation-in
-PostgreSQL%2C-SQL-Server%2C-and-MySQL.html%23extended

Regards,
Igor Neyman

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Bill Moran

In response to Igor Neyman iney...@perceptron.com:

-Original Message-
From: Alexander Farber [mailto:alexander.far...@gmail.com]
Sent: Tuesday, December 28, 2010 10:33 AM
To: pgsql-general@postgresql.org
Subject: Concatenating several rows with a semicolon

Hello,

I'm working on a small app, which receives a list of 20
players in XML format.

The initial version works ok and I use there just 1 SQL
statement and thus it is easy for me to fetch results row by
row and print XML at the same time:

select u.id,
u.first_name,
u.city,
u.avatar,
m.money,
u.login
u.logout as online
from pref_users u,
pref_money m where

m.yw=to_char(current_timestamp, '-IW') and
u.id=m.id
order by m.money desc
limit 20 offset ?

My problem is however, that I need to add more data for each
user representing their statistics over the last 20 weeks.
And that data is in separate tables: pref_money, pref_pass, pref_game:

# select yw, money
from pref_money where id='OK122471020773'
order by yw desc limit 20;
yw| money
-+---
2010-52 | 760
2010-51 | 3848
2010-50 | 4238
2010-49 | 2494
2010-48 | 936
2010-47 | 3453
2010-46 | 3923
2010-45 | 1110
2010-44 | 185
(9 rows)

For example for the table above I'd like to concatenate those
rows and add them as an XML attribute for that user:

user id=OK122471020773 first_name=... city=... ...
pref_money=2010-52:760;2010-51:3848;2010-50:4238; /

so that I can take that attribute in my app and use it in a chart.

My problem is that I don't know how to bring this together in
1 SQL statement (i.e. the SQL statement at the top and then
the concatenated 20 rows from 3 tables).

Is it possible? Maybe I need to write a PgPlSQL procedure for
each of the 3 tables and then add them to the SQL statement
above? But how do I concatenate the rows, should I create a
PgPlSQL variable and always append values to it in a loop or
is there a better way?

Thank you for any hints
Alex

Based on your PG version there are different solutions to your problem.
Not to re-invent the wheel, check this article:
http://www.postgresonline.com/journal/archives/191-String-Aggregation-in-PostgreSQL%2C-SQL-Server%2C-and-MySQL.html%23extended

This doesn't invalidate Igor's response, but you're using XML wrong.

If there are multiple entries for pref_money, then each one should be
a container inside user, i.e.:

user id=bla bla bla ...
pref_money date=2010-52 money=760 /
pref_money date=2010-51 money=3848 /
... etc ...
/user

But then again, it appears as if your yw field is a textual field being
used to store a date, so I expect you have bigger problems coming down
the pike. In all essence, you XML should probably look like this:

user id=bla bla bla ...
pref_money year=2010 week=52 money=760 /
pref_money year=2010 week=51 money=3848 /
... etc ...
/user

And that yw field should be replaced with a week_ending field that is
a date type. You can extract that into year and week using date_part().

Just 15 years of DB experience making me antsy ... does this make me one
of those people who freak out when someone says something wrong on a
message board and just _HAS_ to correct them?

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Alexander Farber

Hello Bill and others,

I don't agree about yw being a bad thing
since I have weekly raings in my app,
but your XML suggestion -

On Tue, Dec 28, 2010 at 9:29 PM, Bill Moran wmo...@potentialtech.com wrote:

 If there are multiple entries for pref_money, then each one should be
 a container inside user, i.e.:

 user id=bla bla bla ...
  pref_money date=2010-52 money=760 /
  pref_money date=2010-51 money=3848 /
  ... etc ...
 /user

is a very good point, thanks!

Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Dmitriy Igrishin

2010/12/29 Alexander Farber alexander.far...@gmail.com

 Hello Bill and others,

 I don't agree about yw being a bad thing
 since I have weekly raings in my app,
 but your XML suggestion -

 On Tue, Dec 28, 2010 at 9:29 PM, Bill Moran wmo...@potentialtech.com
 wrote:
 
  If there are multiple entries for pref_money, then each one should be
  a container inside user, i.e.:
 
  user id=bla bla bla ...
   pref_money date=2010-52 money=760 /
   pref_money date=2010-51 money=3848 /
   ... etc ...
  /user

Well, generally storing data in attributes should be avoided:

user id=id
  pref_money
date.../date
money.../money
  /pref_money
  ...
/user
is a better.


 is a very good point, thanks!

 Alex

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
// Dmitriy.

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Alexander Farber

On Tue, Dec 28, 2010 at 10:31 PM, Dmitriy Igrishin dmit...@gmail.com wrote:
  user id=bla bla bla ...
   pref_money date=2010-52 money=760 /
   pref_money date=2010-51 money=3848 /
   ... etc ...
  /user

 Well, generally storing data in attributes should be avoided:

 user id=id
   pref_money
     date.../date
     money.../money
   /pref_money
   ...
 /user
 is a better.

Attributes give me smaller size...

Regards
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Dmitriy Igrishin

2010/12/29 Alexander Farber alexander.far...@gmail.com

 On Tue, Dec 28, 2010 at 10:31 PM, Dmitriy Igrishin dmit...@gmail.com
 wrote:
   user id=bla bla bla ...
pref_money date=2010-52 money=760 /
pref_money date=2010-51 money=3848 /
... etc ...
   /user
 
  Well, generally storing data in attributes should be avoided:
 
  user id=id
pref_money
  date.../date
  money.../money
/pref_money
...
  /user
  is a better.

 Attributes give me smaller size...

Well, JSON might give you smaller size. Why you need XML then? :-)


 Regards
 Alex

 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general




-- 
// Dmitriy.

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Bill Moran

In response to Alexander Farber alexander.far...@gmail.com:

 Hello Bill and others,
 
 I don't agree about yw being a bad thing
 since I have weekly raings in my app,
 but your XML suggestion -

Do as you like, but I'll bet my reputation that decision will become
an unnecessary limitation for the application at some point in the
future.

At the least, you have completely crippled PostgreSQL's powerful date
arithmetic abilities.  You've also made it so that if you want to
combine results to produce monthly, quarterly, or yearly reports, that
you'll have some crazy regular expressioning going on.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Concatenating several rows with a semicolon

2010-12-28 Thread Alexander Farber

Hello Dmitriy,

I think this combination of attributes and children:

   user id=bla bla bla ...
    pref_money date=2010-52 money=760 /
    pref_money date=2010-51 money=3848 /
    ... etc ...
   /user

will be a good balance between size and my original problem
(combining user data and their stats in 1 chunk of information).

And I don't use JSON, because it is not natively
supported by Flex/Flash and my app is in Flex (here is its pic:
http://stackoverflow.com/questions/4548878/pl-pgsql-concatenating-row-values-to-a-json-like-string
)

  Well, generally storing data in attributes should be avoided:

You haven't backuped your statement by any arguments

Regards
Alex

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Min/Max for partitioned tables

2010-12-28 Thread tmoore


Using 8.4, seeing min/max doing sequential scans for partitioned tables and
noticed similar posts. It wasn't clear to me if 9.0 addressed this. Wondered
what the state of this was.

Thanks
Tom 
-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Min-Max-for-partitioned-tables-tp3320797p3320797.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Restore problem

2010-12-28 Thread Bob Pawley

Hi

I have restored a database using psql to windows version 8.4.

During the restore the trigger code became jumbled. 

I now have a great number of lines that have moved so that they are now 
included in  lines the have been commented out – not to mention that the code 
is hard to read.

Is there some way of correcting this – or re restoring the database, so that I 
don’t have to go through the whole code line by line?

Bob

53 matches

Mail list logo