pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedwhere to start with this procedure ?
4+ messages / 3 participants
[nested] [flat]
* where to start with this procedure ?
@ 2004-05-20 00:33 Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas @ 2004-05-20 00:33 UTC (permalink / raw)
To: pgsql-sql
Hi,
I know a bit of SQL but not exactly a lot so I ran into this problem.
I have tables on the server that get joined in a view.
That's OK.
Now I need just a couple of records say 10-100 of 30 000 which could
easily be filtered by a integer key.
As of now I have to pull the whole lot into Access and let it do the
filtering. That doesn't play nice on our network.
Could someone kick me into the right direction where to learn stuff like:
function grabem(x integer) recordset
(
grabem = select * from my_view where key = x
)
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: where to start with this procedure ?
@ 2004-05-20 07:17 Andrei Bintintan <klodoma@ar-sd.net>
parent: Andreas <maps.on@gmx.net>
0 siblings, 1 reply; 4+ messages in thread
From: Andrei Bintintan @ 2004-05-20 07:17 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
Hi,
Use the Offset and Limit in the SQL query.
http://developer.postgresql.org/docs/postgres/queries-limit.html
For example:
SELECT select_list
FROM table_expression
WHERE condition
Let's supose that this query returnes normaly 30000 elements.
SELECT select_list
FROM table_expression
WHERE condition
LIMIT 50
OFFSET 10000
This query will return 50 elements starting with the 10000 elements... so
the elemenst from 10000 to 10050.
Hope that helps.
Best regards,
Andy.
----- Original Message -----
From: "Andreas" <maps.on@gmx.net>
To: <pgsql-sql@postgresql.org>
Sent: Thursday, May 20, 2004 3:33 AM
Subject: [SQL] where to start with this procedure ?
> Hi,
> I know a bit of SQL but not exactly a lot so I ran into this problem.
>
> I have tables on the server that get joined in a view.
> That's OK.
>
> Now I need just a couple of records say 10-100 of 30 000 which could
> easily be filtered by a integer key.
> As of now I have to pull the whole lot into Access and let it do the
> filtering. That doesn't play nice on our network.
>
> Could someone kick me into the right direction where to learn stuff like:
>
> function grabem(x integer) recordset
> (
> grabem = select * from my_view where key = x
> )
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: where to start with this procedure ?
@ 2004-05-20 14:46 Andreas <maps.on@gmx.net>
parent: Andrei Bintintan <klodoma@ar-sd.net>
0 siblings, 1 reply; 4+ messages in thread
From: Andreas @ 2004-05-20 14:46 UTC (permalink / raw)
To: Andrei Bintintan <klodoma@ar-sd.net>; +Cc: pgsql-sql
Hi Andrei,
>Use the Offset and Limit in the SQL query.
>[...]
>SELECT select_list
> FROM table_expression
> WHERE condition
> LIMIT 50
> OFFSET 10000
>
>This query will return 50 elements starting with the 10000 elements... so
>the elemenst from 10000 to 10050.
>
>
That isn't the issue since I only need a specific few of the lines at all.
I need a dynamic WHERE clause.
It's more like this :
SELECT customer_id, and, some, more, fields
FROM table1
JOIN table2
JOIN table3
....
This results in the "huge" set.
On the Access-form I only need the orders, or contact history for 1
customer.
As of now I only know 2 ways to solve this.
1) Load all the tables over the net into Access and let the JOIN run
locally. That way I can filter dynamically by adding a WHERE
customer_id = x to the select above.
2) Let the JOINS run in a server based view and transfer the whole
result into Access to fetch the few lines regarding the customer.
There should be something like:
3)
a) Access calls a server based function with the customer_id as parameter.
b) The function calls the server based view and filters the result
acording to it's parameter.
c) The function sends only the actually wanted lines back to Access.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: where to start with this procedure ?
@ 2004-05-20 15:01 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Andreas <maps.on@gmx.net>
0 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2004-05-20 15:01 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: Andrei Bintintan <klodoma@ar-sd.net>; pgsql-sql
On Thu, 20 May 2004, Andreas wrote:
>
> Hi Andrei,
>
>
> >Use the Offset and Limit in the SQL query.
> >[...]
> >SELECT select_list
> > FROM table_expression
> > WHERE condition
> > LIMIT 50
> > OFFSET 10000
> >
> >This query will return 50 elements starting with the 10000 elements... so
> >the elemenst from 10000 to 10050.
> >
> >
>
> That isn't the issue since I only need a specific few of the lines at all.
> I need a dynamic WHERE clause.
>
> It's more like this :
> SELECT customer_id, and, some, more, fields
> FROM table1
> JOIN table2
> JOIN table3
> ....
>
> This results in the "huge" set.
> On the Access-form I only need the orders, or contact history for 1
> customer.
>
> As of now I only know 2 ways to solve this.
> 1) Load all the tables over the net into Access and let the JOIN run
> locally. That way I can filter dynamically by adding a WHERE
> customer_id = x to the select above.
Why not add the where clause to the select that goes to the server?
In any case, you can do a function that does the select with a condition
fairly easily with a set returning function in recent versions I think.
Make a composite type with the return fields (create type <name> as (...))
then make an sql function that takes an integer returns setof <name> that
selects from table1 join table2 ... where customer_id = $1.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-05-20 15:01 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-05-20 00:33 where to start with this procedure ? Andreas <maps.on@gmx.net>
2004-05-20 07:17 ` Andrei Bintintan <klodoma@ar-sd.net>
2004-05-20 14:46 ` Andreas <maps.on@gmx.net>
2004-05-20 15:01 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox