[SQL] Concatenate results of a single column query

2004-04-18 Thread Marco Lazzeri 
Hi All,
I would like to concatenate results of a single column query using
PostgreSQL 7.3.

Something like the coming feature

SELECT
  p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
FROM people AS p

(devel docs for 7.5:
http://developer.postgresql.org/docs/postgres/sql-expressions.html )

Any suggestions?

Thanks
Marco


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Concatenate results of a single column query

2004-04-18 Thread Greg Stark 

Marco Lazzeri <[EMAIL PROTECTED]> writes:

> SELECT
>   p.name, ARRAY(SELECT nicknames FROM people WHERE people.id = p.id)
> FROM people AS p

> Any suggestions?

Something like:

db=> create aggregate array_aggregate (basetype = integer, sfunc = array_append, stype 
= integer[], initcond = '{}');
CREATE AGGREGATE

db=> select array_aggregate(id) from tab;
  array_aggregate  
 

 
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,28,29,30,31,32,33,34,36,37,38,39,40,41,42,43,27,26,44,45,46,47,48,49,50,51,52,53,54,35}
(1 row)

-- 
greg


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [SQL] problem porting MySQL SQL to Postgres

2004-04-18 Thread Markus Bertheau 
Ð ÐÑÐ, 15.04.2004, Ð 13:15, Paul Thomas ÐÐÑÐÑ:
> On 15/04/2004 11:25 Dan Field wrote:

> Your "= NULL" tests are also not valid SQL (should be IS NULL).

I think = NULL _is_ valid SQL, it just doesn't do what you think.

-- 
Markus Bertheau <[EMAIL PROTECTED]>


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [SQL] problem porting MySQL SQL to Postgres

2004-04-18 Thread Paul Thomas 
On 18/04/2004 19:37 Markus Bertheau wrote:
В Чтв, 15.04.2004, в 13:15, Paul Thomas пишет:
> On 15/04/2004 11:25 Dan Field wrote:
> Your "= NULL" tests are also not valid SQL (should be IS NULL).

I think = NULL _is_ valid SQL, it just doesn't do what you think.
It's valid in an assignment

	update foo set bar = null;

PostgreSQL can be coerced into accepting where foo = null with the 
transform_null_equals run-time option but that doesn't make it legal SQL 
IFAIK.

--
Paul Thomas
+--+-+
| Thomas Micro Systems Limited | Software Solutions for 
Business |
| Computer Consultants | 
http://www.thomas-micro-systems-ltd.co.uk   |
+--+-+

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [SQL] problem porting MySQL SQL to Postgres

2004-04-18 Thread Bruno Wolff III 
On Sun, Apr 18, 2004 at 20:24:47 +0100,
  Paul Thomas <[EMAIL PROTECTED]> wrote:
> 
> PostgreSQL can be coerced into accepting where foo = null with the 
> transform_null_equals run-time option but that doesn't make it legal SQL 
> IFAIK.

foo = NULL is a valid SQL expression that always returns NULL.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org