Re: [GENERAL] Counting elements of an array

[Renaud Tthonnart]

Tom Lane wrote:

 Renaud Tthonnart [EMAIL PROTECTED] writes:
  I would like to know how I can get the number of elements of an array.

 There is a function that returns an array's dimensions as a text string:

 regression=# select array_dims( '{1,2,3}'::int[] );
  array_dims
 
  [1:3]
 (1 row)

 regression=# select array_dims( '{{1,2,3},{4,5,6}}'::int[] );
  array_dims
 
  [1:2][1:3]
 (1 row)

 regards, tom lane

Ok Tom, but if I have a table (for example aaa) that contains an array (for
example vector)

The function that you have spoken to me doesn't work:

select array_dims(vector) from aaa;
or
select array_dims(vector :: int[]) from aaa;

The result is :

 array_dims




(3 row)

There isn't  any parse error but it don't work.

Do I have badly understand what you have explain me?

Thank you,
Renaud THONNART



---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [GENERAL] Counting elements of an array

[Renaud Tthonnart]

Sorry!!
It works very well!

Renaud THONNART


 Renaud Tthonnart [EMAIL PROTECTED] writes:
  I would like to know how I can get the number of elements of an array.

 There is a function that returns an array's dimensions as a text string:

 regression=# select array_dims( '{1,2,3}'::int[] );
  array_dims
 
  [1:3]
 (1 row)

 regression=# select array_dims( '{{1,2,3},{4,5,6}}'::int[] );
  array_dims
 
  [1:2][1:3]
 (1 row)

 regards, tom lane


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

[GENERAL] libpq++ : Disconnect a DB

[Renaud Tthonnart]

Good morning everyone,

By declaring a PgDatabase variable like this, I know that I make a
connection to the DB 'test'

PgDatabase data("dbname = test");


How to end connection?
By making this?

delete data;

regards, Renaud THONNART


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

Re: [GENERAL] libpq++ : Disconnect a DB

[Renaud Tthonnart]

Jose Manuel Lorenzo Lopez wrote:

 Renaud Tthonnart schrieb:
 
  Good morning everyone,
 
  By declaring a PgDatabase variable like this, I know that I make a
  connection to the DB 'test'
 
  PgDatabase data("dbname = test");
 
  How to end connection?
  By making this?
 
  delete data;

 Hello Renaud,

 I think yes, because I guess there is an implicit call to disconnect
 your object by calling the destructor. But I am not sure, may be
 any of the developers in this list is able to confirm my point of
 view.

 Best Regards / Un saludo / Mit freundlichen Gren / Cordiali Saluti

 Jos Manuel Lorenzo Lpez


I am thinking just like you!
Thank you to strengthen my intuition

Renaud THONNART


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

Re: [GENERAL] Counting elements of an array

[Renaud Tthonnart]

Tom Lane wrote:

 Renaud Tthonnart [EMAIL PROTECTED] writes:
  I would like to know how I can get the number of elements of an array.

 There is a function that returns an array's dimensions as a text string:

 regression=# select array_dims( '{1,2,3}'::int[] );
  array_dims
 
  [1:3]
 (1 row)

 regression=# select array_dims( '{{1,2,3},{4,5,6}}'::int[] );
  array_dims
 
  [1:2][1:3]
 (1 row)

 regards, tom lane

Thanks very much Tom,  you have helped me a lot.
Renaud THONNART

[GENERAL] Counting elements of an array

[Renaud Tthonnart]

Good morning all,

I would like to know how I can get the number of elements of an array.

regards,
Renaud THONNART

Re: [GENERAL] Counting elements of an array

[Renaud Tthonnart]

Christopher Sawtell wrote:

 On Thu, 01 Mar 2001 21:53, you wrote:
  Good morning all,
 
  I would like to know how I can get the number of elements of an array.

 create function array_element_count(_int4) returns integer as '
  declare
   a alias for $1;
   i integer;
  begin
   i := 1;
   while a[i] loop
i := i+1;
   end loop;
  return i-1;
  end;' language 'plpgsql' with(isstrict,iscachable);


Thanks a lot
Regards, Renaud THONNART

[GENERAL] Object

[Renaud Tthonnart]

Can I create object and methods with postgreSQL.
And how?

Thank in advance,
Renaud THONNART

[GENERAL] Nested tables

[Renaud Tthonnart]

How can I create nested tables ?
I haven't find how it can be made in the doc.

Could someone give me a little example to create and then insert in
nested table?

Thank you.
Renaud THONNART

[GENERAL] Create type

[Renaud Tthonnart]

If I can't use nested tables, how can I create a
type that will be a kind of  "dynamic list of structure".

regards,
Renaud THONNART

Re: [GENERAL] Query with multiples operators BETWEEN

[Renaud Tthonnart]

Tom Lane wrote:


 Do a VACUUM ANALYZE, for starters.  These results look like the planner
 is working with the initial default estimates for a never-vacuumed table.


 Indexes might be a good idea too.
 http://www.postgresql.org/devel-corner/docs/postgres/indices.html
 has a good intro to the basics.

 regards, tom lane

The qwery  I have problem with is always :

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5
  AND o.DE BETWEEN 2 AND 7;

EXPLAIN result is :

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..56.67 rows=3 width=20)
  -  Seq Scan on observation o  (cost=0.00..30.00 rows=1 width=4)
  -  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)

EXPLAIN


I have created an index on columns ra and de :
CREATE INDEX ra_de_ind ON observation(ra,de);
CLUSTER ra_de_ind ON observation;
VACUUM observation;


EXPLAIN result becomes :

Nested Loop  (cost=0.00..28.69 rows=3 width=20)
  -  Index Scan using ra_de_ind on observation o  (cost=0.00..2.03 rows=1
width=4)
  -  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)


But the qwery is always not able to find what I'm looking for.

Renaud THONNART

Re: [GENERAL] sequence and stored procedure

[Renaud Tthonnart]

Stephan Szabo wrote:

 On Wed, 21 Feb 2001, Renaud Tthonnart wrote:

  How can I use sequences in a stored procedure written with pl/pgsql?

 What exactly are you trying to do?  Pretty much you can call the
 sequence functions you want to use on the sequence name you
 want (you have to double the quotes around the name since it's
 inside the quoted text of the function.

 create function foo() returns opaque as
 '
 begin
  NEW.y:=nextval(''seqseq'');
  return NEW;
 end;' language 'plpgsql';

 create trigger footrig before insert or update on testfoo for
 each row execute procedure foo();

Thank for your answer! That will help me !
Renaud THONNART

[GENERAL] Query with multiples operators BETWEEN

[Renaud Tthonnart]

This qwery takes about 2 seconds :

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5;

This one about 5 seconds :

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.DE BETWEEN 2 AND 7;

And this takes a lot of time (I can't say how much because I 've
always given it up before its end,
 at least
more than 10 minutes)

SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5
  AND o.DE BETWEEN 2 AND 7;

Could someone help or explain me?

Thanks in advance and excuse my bad English
Renaud THONNART

Re: [GENERAL] Query with multiples operators BETWEEN

[Renaud Tthonnart]

Here they are :

EXPLAIN
(SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5);


NOTICE:  QUERY PLAN:

Hash Join  (cost=25.02..60.98 rows=33 width=20)
  -  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)
  -  Hash  (cost=25.00..25.00 rows=10 width=4)
-  Seq Scan on observation o  (cost=0.00..25.00 rows=10
width=4)

EXPLAIN



EXPLAIN
(SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.DE BETWEEN 2 AND 7);

NOTICE:  QUERY PLAN:

Hash Join  (cost=25.02..60.98 rows=33 width=20)
  -  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)
  -  Hash  (cost=25.00..25.00 rows=10 width=4)
-  Seq Scan on observation o  (cost=0.00..25.00 rows=10
width=4)

EXPLAIN


---

EXPLAIN
(SELECT e.name
FROM Observation o, Exposure_EPIC e
WHERE o.numObs = e.obs
  AND e.instrPN IS NOT NULL
  AND o.RA BETWEEN 3 AND 5
  AND o.DE BETWEEN 2 AND 7);

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..56.67 rows=3 width=20)
  -  Seq Scan on observation o  (cost=0.00..30.00 rows=1 width=4)
  -  Seq Scan on exposure_epic e  (cost=0.00..22.50 rows=333 width=16)

EXPLAIN
--
Table Observation has 5000 rows  and  10 columns
Table Exposure_Epic has 45000 rows and 6 columns
I haven't create any index
If use---  2 and 7 , this is the same result.

Thank you for your help
Renaud THONNART

[GENERAL] sequence and stored procedure

[Renaud Tthonnart]

How can I use sequences in a stored procedure written with pl/pgsql?

Thanks in advance!
Renaud THONNART