[SQL] ARRAY_AGG and COUNT
Hi all!
I am working on a query to identify which group ids exists within a spatial
cell. In this case i have the GREG table which has polygon data and the
priogrid_land which have the cell polygon.
I want to identify which and how many GREG group ids exists within each of
the priogrid_land cells.
I have tried the below query, but i cannot find a way to select only
DISTINCT into the ARRAY_AGG function. I also do not want 0 to be included in
the array or 0 to be counted.
Any suggestions on how to accomplish the above?
Thanks!
Code:
SELECT priogrid_land.gid, priogrid_land.cell,
array_to_string(array_agg(g1id), ';') AS g1list,
array_to_string(array_agg(g2id), ';') AS g2list,
array_to_string(array_agg(g3id), ';') AS g3list,
count(distinct g1id) AS g1count, count(distinct g2id) AS g2count,
count(distinct g3id) AS g3count
INTO greg_list
FROM "GREG", priogrid_land WHERE ST_Intersects("GREG".the_geom,
priogrid_land.cell)
GROUP BY priogrid_land.gid, priogrid_land.cell;
This will give this result:
g1list; g2list; g3list; g1count; g2count; g3count
"482";"0";"0";1;1;1
"23;482;482";"0;0;0";"0;0;0";2;1;1
"1227;482;23";"0;0;0";"0;0;0";3;1;1
"1227;23;482;66";"0;0;0;0";"0;0;0;0";4;1;1
"1227;23;66";"0;0;0";"0;0;0";3;1;1
As you see i.e. in g1list 482 is counted twice in row 2. 0 is also counted.
The distinct works for count, but not for array_agg.
Cheers.
Re: [SQL] After insert trigger and select
> -Original Message- > From: Rok Jaklič [mailto:r...@rasca.net] > Sent: Wednesday, February 16, 2011 5:35 PM > To: pasman pasmański; pgsql-sql > Subject: Re: After insert trigger and select > > On 02/16/2011 08:46 PM, pasman pasmański wrote: > >> If I have after insert trigger on some table which updates > some data > >> in that same table, will be the select statement from some other > >> client executed after all statements in that trigger? > >> > > select statement is fired before commit ? > > > > pasman > For example let us say that trigger takes a long time to end. > Are all statements in trigger executed before select from > "outside" if select is called somewhere between executing of > the trigger? > With MVCC "writers" don't block "readers", and "readers" don't block "writers". Read PG docs on MVCC. Regards, Igor Neyman -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ARRAY_AGG and COUNT
On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen
wrote:
> Hi all!
>
> I have tried the below query, but i cannot find a way to select only
> DISTINCT into the ARRAY_AGG function. I also do not want 0 to be included in
> the array or 0 to be counted.
>
> Code:
>
> SELECT priogrid_land.gid, priogrid_land.cell,
> array_to_string(array_agg(g1id), ';') AS g1list,
> array_to_string(array_agg(g2id), ';') AS g2list,
> array_to_string(array_agg(g3id), ';') AS g3list,
> count(distinct g1id) AS g1count, count(distinct g2id) AS g2count,
> count(distinct g3id) AS g3count
> INTO greg_list
> FROM "GREG", priogrid_land WHERE ST_Intersects("GREG".the_geom,
> priogrid_land.cell)
> GROUP BY priogrid_land.gid, priogrid_land.cell;
>
>
> As you see i.e. in g1list 482 is counted twice in row 2. 0 is also counted.
> The distinct works for count, but not for array_agg.
>
I don't have a version earlier than 8.4, but in 8.4+ you can use
DISTINCT in array_agg().
Eg.
array_to_string(array_agg(distinct g1id),';') as ...
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] ARRAY_AGG and COUNT
Great. Thanks. Do you have a suggestion on how to ignore the group id's with
0 as value?
I dont want these to be counted.
Thanks!
2011/2/17 bricklen
> On Thu, Feb 17, 2011 at 6:20 AM, Andreas Forø Tollefsen
> wrote:
> > Hi all!
> >
> > I have tried the below query, but i cannot find a way to select only
> > DISTINCT into the ARRAY_AGG function. I also do not want 0 to be included
> in
> > the array or 0 to be counted.
> >
> > Code:
> >
> > SELECT priogrid_land.gid, priogrid_land.cell,
> > array_to_string(array_agg(g1id), ';') AS g1list,
> > array_to_string(array_agg(g2id), ';') AS g2list,
> > array_to_string(array_agg(g3id), ';') AS g3list,
> > count(distinct g1id) AS g1count, count(distinct g2id) AS g2count,
> > count(distinct g3id) AS g3count
> > INTO greg_list
> > FROM "GREG", priogrid_land WHERE ST_Intersects("GREG".the_geom,
> > priogrid_land.cell)
> > GROUP BY priogrid_land.gid, priogrid_land.cell;
> >
> >
> > As you see i.e. in g1list 482 is counted twice in row 2. 0 is also
> counted.
> > The distinct works for count, but not for array_agg.
> >
>
> I don't have a version earlier than 8.4, but in 8.4+ you can use
> DISTINCT in array_agg().
> Eg.
> array_to_string(array_agg(distinct g1id),';') as ...
>
Re: [SQL] ARRAY_AGG and COUNT
On Thu, Feb 17, 2011 at 12:11 PM, Andreas Forø Tollefsen wrote: > Great. Thanks. Do you have a suggestion on how to ignore the group id's with > 0 as value? > I dont want these to be counted. You can probably select your values in a subquery and filter out the 0 value results in the WHERE clause, then apply your existing query (with distinct array_agg) to the outer query. Or another way would be to use a CASE statement to skip the 0 values. eg. array_to_string(array_agg(distinct (case when g1id <> 0 then g1id end) ),';') ... The array_to_string transformation should trim out the NULLs from the CASE statement. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Function compile error
Title: Thanks Igor Nayman!!! The function worked for me Sivannarayanareddy Nusum | System Analyst(Moneta GDO) Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, Bangalore – 560037, India. Phone: +91 80 6696 3371; Mobile: +91 9902065831 Fax: +91 80 6696 ; Email: sivannarayanre...@subexworld.com; URL: www.subexworld.com Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html On 2/16/2011 9:54 PM, Igor Neyman wrote: -Original Message- From: Sivannarayanreddy [mailto:sivannarayanre...@subexworld.com] Sent: Wednesday, February 16, 2011 7:36 AM To: pgsql-sql@postgresql.org Subject: Function compile error Hello, I am trying to create the function as below but it is throwing error 'ERROR: syntax error at or near "DECLARE"', Could some one help me please CREATE FUNCTION check_password(databasename text, tablename text, indexname text)RETURNS VOID AS DECLARE v_count INTEGER; BEGIN select count(1) into v_count from pg_index inx where inx.indexrelid in (select oid from pg_class where relname=$3 and relowner in (select oid from pg_authid where rolname=$1)) and inx.indrelid in (select oid from pg_class where relname=$2 and relowner in (select oid from pg_authid where rolname=$1)); if v_count = 0 then execute immediate 'create unique index $3 on $2 (acn_id)'; end if; END; Sivannarayanareddy Nusum | System Analyst(Moneta GDO) Subex Limited, Adarsh Tech Park, Outer Ring Road, Devarabisannalli, Bangalore - 560037, India. Phone: +91 80 6696 3371; Mobile: +91 9902065831 Fax: +91 80 6696 ; Email: sivannarayanre...@subexworld.com ; URL: www.subexworld.com Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html CREATE FUNCTION check_password(databasename text, tablename text, indexname text) RETURNS VOID AS $body$ DECLARE v_count INTEGER; BEGIN select count(1) into v_count from pg_index inx where inx.indexrelid in (select oid from pg_class where relname=$3 and relowner in (select oid from pg_authid where rolname=$1)) and inx.indrelid in (select oid from pg_class where relname=$2 and relowner in (select oid from pg_authid where rolname=$1)); if v_count = 0 then execute immediate 'create unique index $3 on $2 (acn_id)'; end if; END; $body$LANGUAGE PLPGSQL; Regards, Igor Neyman .
Re: [SQL] Is it possible to get DISTINCT rows from RETURNING clause?
Any ideas? -Original Message- From: Gnanakumar [mailto:gna...@zoniac.com] Sent: Thursday, February 17, 2011 12:36 PM To: pgsql-sql@postgresql.org Subject: Is it possible to get DISTINCT rows from RETURNING clause? Hi, Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING clause? "MYTABLE" columns are: APRIMARYKEYCOLUMN ABOOLEANCOLUMN EMAIL COLUMN1 COLUMN2 COLUMN3 UPDATE using RETURNING clause query: UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL = MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3; Here in this case, I expect to return distinct rows from these columns: EMAIL, COLUMN1, COLUMN2, COLUMN3. I even tried out some ways of getting distinct rows, but it doesn't work. Though I can still solve this at application layer, I'm trying to find whether this could be controlled at query-level. Any different ideas/suggestions are appreciated. Regards, Gnanam -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Retrieve the column values of a record without knowing the names
Hello, I've got the following function and I want to access the fields values of my record by index. The problem is that my select is retrieving each record line with all values and not each one of each row on my view... How can I solve this problem? Thanks in advance. CREATE FUNCTION fc_teste_tce(aluno integer) RETURNS character varying AS $BODY$ DECLARE reg record; BEGIN for reg in execute 'SELECT ARRAY (SELECT vw_teste_tce FROM estagio.vw_teste_tce where aluno = ''3043'' LIMIT 20) AS campos' loop for j in 1..array_upper(reg.campos,1) loop raise notice 'Field Value: %',reg.campos[j]; end loop; end loop; return 'ok'; END; $BODY$ LANGUAGE plpgsql VOLATILE; -- View this message in context: http://postgresql.1045698.n5.nabble.com/Retrieve-the-column-values-of-a-record-without-knowing-the-names-tp3387932p3387932.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
