On Wed, Oct 17, 2007 at 01:00:48PM -0500, Josh Trutwin wrote:
> Works like a champ on 8.1.
>
> Thanks!
That's OK, it was a good learning experience.
> Did you see David Fetter's reply to the original post? He has
> an interesting alternative.
I did, it's much more elegant. I've never seen gen
> >
> >
> > > CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
> > > RETURNS ANYARRAY
> > > LANGUAGE SQL
> > > AS $$
> > > SELECT ARRAY(
> > > SELECT $1[i] AS "the_intersection"
> > > FROM generate_series(
> > > array_lower($1,1),
> > > array_upper($1,1)
> > >
On Wed, 17 Oct 2007 11:26:05 -0700
David Fetter <[EMAIL PROTECTED]> wrote:
> > Doesn't appear to work on 8.1:
> >
> > psql=> select array_intersect('{1,2,3}', '{2,3,4}');
> > ERROR: could not determine anyarray/anyelement type because input
> > has type "unknown"
>
> As mentioned in the "releas
On Wed, Oct 17, 2007 at 01:06:35PM -0500, Josh Trutwin wrote:
> On Wed, 17 Oct 2007 10:04:21 -0700
> David Fetter <[EMAIL PROTECTED]> wrote:
>
>
>
> > CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
> > RETURNS ANYARRAY
> > LANGUAGE SQL
> > AS $$
> > SELECT ARRAY(
> > SELECT $
On Wed, 17 Oct 2007 10:04:21 -0700
David Fetter <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
> RETURNS ANYARRAY
> LANGUAGE SQL
> AS $$
> SELECT ARRAY(
> SELECT $1[i] AS "the_intersection"
> FROM generate_series(
> array_lower($1,1),
On Wed, 17 Oct 2007 17:49:01 +0100
Sam Mason <[EMAIL PROTECTED]> wrote:
> On Wed, Oct 17, 2007 at 11:31:51AM -0500, Josh Trutwin wrote:
> > Is the =ANY specific to PG 8.2 or higher? On 8.1.10:
>
> It appears (according to [1] and [2]) that you may be able to just
> remove the '=' to get it worki
On Wed, 17 Oct 2007 17:42:21 +0100
Sam Mason <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION array_intersect (array1
> INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
> DECLARE
>out INTEGER[];
> BEGIN
> out := '{}'::INTEGER[];
> IF array1 IS NUL
On Wed, Oct 17, 2007 at 10:19:43AM -0500, Josh Trutwin wrote:
> Hi,
>
> Is it possible to find the intersection of two array values?
>
> a = '{1,2,3}'
> b = '{2,3,4}'
>
> a intersect b = '{2,3}'
>
> Assume I need to write a pl/pgsql function to do this.
You can use an SQL function, which has t
On Wed, Oct 17, 2007 at 11:31:51AM -0500, Josh Trutwin wrote:
> Is the =ANY specific to PG 8.2 or higher? On 8.1.10:
It appears (according to [1] and [2]) that you may be able to just
remove the '=' to get it working with 8.1.x. i.e.
v ANY ('{1,2}')
is correct in 8.1.x but in 8.2.x it's
v
On Wed, Oct 17, 2007 at 11:28:31AM -0500, Josh Trutwin wrote:
> It's inelegant, but I just did this:
> IF return_empty THEN
> RETURN '{}';
> END IF;
humm, why didn't that seem to work for me... ah well. Next version
fixes a problem that I didn't test of the inputs being NULL
On Wed, 17 Oct 2007 12:33:13 -0400
"Merlin Moncure" <[EMAIL PROTECTED]> wrote:
> On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > On Wed, 17 Oct 2007 11:12:27 -0500
> > "Rodrigo De León" <[EMAIL PROTECTED]> wrote:
> >
> > > On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > > > nm -
On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> On Wed, 17 Oct 2007 11:12:27 -0500
> "Rodrigo De León" <[EMAIL PROTECTED]> wrote:
>
> > On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > > nm - I just wrote a function - though curious if this is the most
> > > effecient way:
> >
> > If
On Wed, 17 Oct 2007 17:08:06 +0100
Sam Mason <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION array_intersect (array1
> INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
> DECLARE
>out INTEGER[];
> BEGIN
> IF array1 IS NULL OR array2 IS NULL THEN
>R
> This is only going to work for one-dimensional arrays (I'm not sure
> how you would ever fix that with the support postgres has for
> arrays) but the (computational) complexity of having an embedded
> FOR loops looks bad for performance. As you can already use '=ANY'
> syntax to search inside
On Wed, 17 Oct 2007 11:12:27 -0500
"Rodrigo De León" <[EMAIL PROTECTED]> wrote:
> On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > nm - I just wrote a function - though curious if this is the most
> > effecient way:
>
> If you only want TRUE or FALSE, you can use '&&':
>
> t=# SELECT '{1
On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> nm - I just wrote a function - though curious if this is the most
> effecient way:
If you only want TRUE or FALSE, you can use '&&':
t=# SELECT '{1,2}'::INT[] && '{2,3}'::INT[];
?column?
--
t
(1 row)
---(end
On Wed, Oct 17, 2007 at 10:37:23AM -0500, Josh Trutwin wrote:
> On Wed, 17 Oct 2007 10:19:43 -0500
> Josh Trutwin <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> >
> > Is it possible to find the intersection of two array values?
> >
> > a = '{1,2,3}'
> > b = '{2,3,4}'
> >
> > a intersect b = '{2,3}'
> >
On Wed, 17 Oct 2007 10:19:43 -0500
Josh Trutwin <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is it possible to find the intersection of two array values?
>
> a = '{1,2,3}'
> b = '{2,3,4}'
>
> a intersect b = '{2,3}'
>
> Assume I need to write a pl/pgsql function to do this.
nm - I just wrote a functi
Hi,
Is it possible to find the intersection of two array values?
a = '{1,2,3}'
b = '{2,3,4}'
a intersect b = '{2,3}'
Assume I need to write a pl/pgsql function to do this.
Josh
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
19 matches
Mail list logo