Обсуждение: ECPG Questions

G'Day !

I am writing an application that intefaces with a POstgreSQL database thru
ECPG.

One of my queries is rather complex involving 4+ tables.  One table has an
arbitrary number of items associated with a single entry on another table. 
It is actually a many to many relationship.  I am using a "index" table to
relate the two.

For example:  I have a table that contains an id plus other information
then a second table that contains an id as a foriegn key plus an arbitray
number of data points.  the two table are associated with an index table.

I want to retrieve the data and put it into a linked list of structs, the
data points in an
array inside the struct.

Is there an easy way to do this.

I was think that I would open a cursor to get the info from the first table
(which includes a column for number of data points) Then allocate an array
and open a second cursor to retrieve the data points, then put all the data
into a list element and move to the next truple.

Is this legal ?  Is there a better way ?

The alternative is to get all the data in one large truple and throw away
all the repetive data returned.  This seems like an unneeded headache.

comments and suggestions

cheers,
Jim Parker

On Thu, Jul 11, 2002 at 11:01:27AM -0400, Jim Parker wrote:
> For example:  I have a table that contains an id plus other information
> then a second table that contains an id as a foriegn key plus an arbitray
> number of data points.  the two table are associated with an index table.
> 
> I want to retrieve the data and put it into a linked list of structs, the
> data points in an
> array inside the struct.

The data points are not stored as one attribute of array type, but
rather as several tuples?

> Is there an easy way to do this.
> 
> I was think that I would open a cursor to get the info from the first table
> (which includes a column for number of data points) Then allocate an array
> and open a second cursor to retrieve the data points, then put all the data
> into a list element and move to the next truple.

You can use an empty pointer to that array, libecpg will allocate the
memory if you fill the array at once. No need to use a cursor.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

On Fri, Jul 12, 2002 at 10:45:06AM -0400, Jim Parker wrote:
> I'll try to be a little more specific.
> ...

BTW which datatypes are you using in PGSQL?

> The second way I have thought to do it is to include the vertex table in
> the first query.  This would give all the information I need, but would
> require addition processing to distill the x, y, z information from the
> color, size, etc. information I get form the first truple.

Sorry, I still do not really understand that. Well maybe that's due to
me answering mails while doing something else, but anyway, it would be a
wise idea to let SQL handle as much of the work as possible.

> The first method requires ( I believe ) me to open a cursor. Retrieve the
> truples, allocate memory for a vertex array, Open a cursor to get the
> vertex info put into array, add data to linked list, close second cursor,
> look at next tuple in first cursor.

Which certainly doesn't pose a problem.

> the second method is to open the cursor retrieve data from first tuple, get
> next tuple, check polyine id, if same as current get x, y, z info.
> 
> It seems to me the first method is pefered way of doing things.  Is there
> an easy way to do it ?

I'm really at a loss guessing what you mean here. Where's the problem?
You did describe your solution in a way that implementing should be
straightforward.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

I'll try to be a little more specific.

The appliction is a vector drawing program (CAD).  Because I have no idea
what the drawing looks like of how many items to draw, etc., each primative
type is stored in a linked list of structs (this may change in the future.
Designed to be independent of database storage).

I also have a seperate linked list for vertex and vector information since
multiple drawing primatives may share that data (avoids storing duplicate
info, and avoids updating multiple lists when vertex info is modified (ie
drag/frop)).  I use pointers between lists to share this information.

One of my primative types is the polyline.  I have no idea how many vertex
points the line may contain, but I have an entry in the polylines table for
this information.

In my SQL tables I have tried to normalize that data.  So all the data that
is common to all drawing primatives is in a table called entites (color,
size, linetypes, etc.) and the unique information is in another (one or
more).  Again vertex and vector info gets thier own tables.

So lets look at the polyline example.  I fun a query on the
entites/polyline  tables to get all entities of the polyline type.  This
gives me entity_id, polyline_id, color, size, linetype and number of
vertices, etc.  But this did not give me the x, y, z cooridinates of each
point.  So in this senerio I would need a second query to the vertex table
to get this information.

The second way I have thought to do it is to include the vertex table in
the first query.  This would give all the information I need, but would
require addition processing to distill the x, y, z information from the
color, size, etc. information I get form the first truple.

The first method requires ( I believe ) me to open a cursor. Retrieve the
truples, allocate memory for a vertex array, Open a cursor to get the
vertex info put into array, add data to linked list, close second cursor,
look at next tuple in first cursor.

the second method is to open the cursor retrieve data from first tuple, get
next tuple, check polyine id, if same as current get x, y, z info.

It seems to me the first method is pefered way of doing things.  Is there
an easy way to do it ?

hope this explains my proiblem better.  Thanks for your help.

cheers,
Jim Parker




On Fri, 12 Jul 2002, Michael Meskes wrote:
> Date: Fri, 12 Jul 2002 15:58:04 +0200
> To: Jim Parker <hopeye@cfl.rr.com>
> From: Michael Meskes <meskes@postgresql.org>
> CC: PostgreSQL mailing list <pgsql-interfaces@postgresql.org>
> Sender: pgsql-interfaces-owner@postgresql.org
> Subject: Re: [INTERFACES] ECPG Questions
> 
> On Thu, Jul 11, 2002 at 11:01:27AM -0400, Jim Parker wrote:
> > For example:  I have a table that contains an id plus other information
> > then a second table that contains an id as a foriegn key plus an
> arbitray
> > number of data points.  the two table are associated with an index
> table.
> > 
> > I want to retrieve the data and put it into a linked list of structs,
> the
> > data points in an
> > array inside the struct.
> 
> The data points are not stored as one attribute of array type, but
> rather as several tuples?
> 
> > Is there an easy way to do this.
> > 
> > I was think that I would open a cursor to get the info from the first
> table
> > (which includes a column for number of data points) Then allocate an
> array
> > and open a second cursor to retrieve the data points, then put all the
> data
> > into a list element and move to the next truple.
> 
> You can use an empty pointer to that array, libecpg will allocate the
> memory if you fill the array at once. No need to use a cursor.
> 
> Michael
> -- 
> Michael Meskes
> Michael@Fam-Meskes.De
> Go SF 49ers! Go Rhein Fire!
> Use Debian GNU/Linux! Use PostgreSQL!
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org