Re: [SQL] Returning a reference to a cursor from a function
On Tuesday 17 Sep 2002 7:12 pm, you wrote:
> Richard,
>
> Thanks for the information. I've made some modifications to your code here
> so that it does a RAISE NOTICE in each loop returning simply the value of n
> and then when the loop is finished it again returns n.
>
> This works fine at the psql level but after it passes through ODBC to the
> ASP layer all I get is the final RETURN value.
Yep - the NOTICE is really a type of error message (you can use RAISE to
generate errors too) and isn't part of your data-stream.
> I have tried using the RETURN function in the loop but it terminates the
> loop.
Indeed it does.
> I really need to return each record up to the ASP layer.
The solution to this sort of thing in version 7.3 is something called table
functions, but I think they're limited to C at the moment, not plpgsql.
With 7.2 you need to return the cursor from the function and then FETCH from
it. An example was missed out from the 7.2.1 docs but you can see one in the
developer's docs (bottom of page):
http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html
To hack our example a bit more the below takes a cursor-name and table name
and defines a cursor for you.
Note that when using it, you need to be within BEGIN...COMMIT (an explicit
transaction) since the cursor returned from the function only lasts until the
end of a transaction.
HTH
- Richard Huxton
DROP FUNCTION foo_count(refcursor, text);
CREATE FUNCTION foo_count(refcursor, text) RETURNS refcursor AS '
DECLARE
curs ALIAS FOR $1;
tbl_name ALIAS FOR $2;
BEGIN
RAISE NOTICE ''cursor on table: %'',tbl_name;
OPEN curs FOR EXECUTE ''SELECT * FROM '' || tbl_name;
RETURN curs;
END;
'language 'plpgsql';
richardh=> BEGIN;
BEGIN
richardh=> SELECT foo_count('fake_cursor','companies');
NOTICE: cursor on table: companies
foo_count
-
fake_cursor
(1 row)
richardh=> FETCH 3 FROM fake_cursor;
co_id | co_name | co_postcode | co_lastchg
---+--+-+---
56 | Acme Associates GmBH | unknown | 2002-06-12
14:04:43.123408+01
57 | Imperial Investments Inc | unknown | 2002-06-12
14:04:43.123408+01
58 | Universal Associates USA | unknown | 2002-06-12
14:04:43.123408+01
(3 rows)
richardh=> COMMIT;
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] Returning a reference to a cursor from a function
Thanks again Richard. I did find that dev note and I do have a version of this working but of course it does not return up to the ASP layer. Since I need to integrate Postgresql ( or something else ) into an existing application using COM as the middle and ASP as the upper layer I must create a function similar to how MS SQL Server handles it. Ah well Thankgs again for you help and I will keep watch on 7.3. Do you know if the ODBC Driver will also be updated to accomodate this function or is the DECLARE/FETCH setting enough. Thanks Dave - Original Message - From: Richard Huxton Sent: Wednesday, September 18, 2002 6:32 AM To: david williams Cc: [EMAIL PROTECTED] Subject: Re: [SQL] Returning a reference to a cursor from a function On Tuesday 17 Sep 2002 7:12 pm, you wrote:> Richard,>> Thanks for the information. I've made some modifications to your code here> so that it does a RAISE NOTICE in each loop returning simply the value of n> and then when the loop is finished it again returns n.>> This works fine at the psql level but after it passes through ODBC to the> ASP layer all I get is the final RETURN value.Yep - the NOTICE is really a type of error message (you can use RAISE to generate errors too) and isn't part of your data-stream.> I have tried using the RETURN function in the loop but it terminates the> loop.Indeed it does.> I really need to return each record up to the ASP layer.The solution to this sort of thing in version 7.3 is something called table functions, but I think they're limited to C at the moment, not plpgsql.With 7.2 you need to return the cursor from the function and then FETCH from it. An example was missed out from the 7.2.1 docs but you can see one in the developer's docs (bottom of page):http://developer.postgresql.org/docs/postgres/plpgsql-cursors.htmlTo hack our example a bit more the below takes a cursor-name and table name and defines a cursor for you.Note that when using it, you need to be within BEGIN...COMMIT (an explicit transaction) since the cursor returned from the function only lasts until the end of a transaction.HTH- Richard HuxtonDROP FUNCTION foo_count(refcursor, text);CREATE FUNCTION foo_count(refcursor, text) RETURNS refcursor AS 'DECLARE curs ALIAS FOR $1; tbl_name ALIAS FOR $2;BEGIN RAISE NOTICE ''cursor on table: %'',tbl_name; OPEN curs FOR EXECUTE ''SELECT * FROM '' || tbl_name; RETURN curs;END;'language 'plpgsql';richardh=> BEGIN;BEGINrichardh=> SELECT foo_count('fake_cursor','companies');NOTICE: cursor on table: companies foo_count-fake_cursor(1 row)richardh=> FETCH 3 FROM fake_cursor;co_id | co_name | co_postcode | co_lastchg---+--+-+--- 56 | Acme Associates GmBH | unknown | 2002-06-12 14:04:43.123408+01 57 | Imperial Investments Inc | unknown | 2002-06-12 14:04:43.123408+01 58 | Universal Associates USA | unknown | 2002-06-12 14:04:43.123408+01(3 rows)richardh=> COMMIT;---(end of broadcast)---TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])Get more from the Web. FREE MSN Explorer download : http://explorer.msn.com
[SQL] Creating a field that uses totals other fields?
I am wanting to create a table that will sum up the total of an order and automatically put value in the specified field. An example of the table is below. Any ideas on how to do this. CREATE TABLE sales_info ( sales_id serial PRIMARY KEY, prod_id int REFERENCES product (prod_id), qty int, unit_price numeric(4,2), sub_total numeric(4,2), tax numeric(4,2), shipping numeric(4,2), grand_total numeric(4,2)); The grand total is where I want the sum of the unit_price, tax, and shipping price entered automatically. Thanks for any help. --Sloan ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] TSQL2 (Temporal SQL) support for Postgres
Is anyone aware of any projects that support the use of TSQL2 or similar functionality working with Postgres? TIA, Ewan Grantham ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Query to evaluate space used
This may or may not be original, but I cobbled it together and thought it
might be useful: a query that reports how much space is used by each
table in your database, including TOAST and TOAST-IDX tables.
This particular version is a bit fancy because it shows the top 20 by
space, followed by a row for "All Others". You could eliminate the half
starting with 'union', and take out the 'limit 20' clause if you wanted
to see them all.
select "Table", "KRows", "MB" from
(select 1 as sort_order, * from (select min(relname) as "Table",
to_char(max(reltuples)/1000,'9990.9') as "KRows",
sum(relpages)/128 as "MB" from (
select relname, '', reltuples, relpages
from pg_class
where relkind = 'r'
union all
select a.relname, b.relname, 0, b.relpages
from pg_class a
join pg_class b
on (b.relname like 'pg_toast_' || a.relfilenode || '%')
where a.relkind = 'r'
) as pg_class
group by relname
order by sum(relpages) desc limit 20) as top_20
union
select 2, 'All Others', to_char(sum("KRows"),'9990.9'),
sum("MB")
from (
select min(relname) as "Table",
sum(reltuples)/1000 as "KRows",
sum(relpages)/128 as "MB" from (
select relname, '', reltuples, relpages
from pg_class
where relkind = 'r'
union all
select a.relname, b.relname, 0, b.relpages
from pg_class a
join pg_class b
on (b.relname like 'pg_toast_' || a.relfilenode || '%')
where a.relkind = 'r'
) as pg_class
group by relname
order by sum(relpages) desc offset 20) as "Others") as rows
order by sort_order, "MB" desc
--
Jeff Boes vox 269.226.9550 ext 24
Database Engineer fax 269.349.9076
Nexcerpt, Inc. http://www.nexcerpt.com
...Nexcerpt... Extend your Expertise
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Performance w/ multiple WHERE clauses
I am running into a serious performance issue with some basic queries.
If I run something like
select * from "Calls" WHERE
( ("CallType" = 'LONG DIST' ))
The search takes about 15 seconds
if I run
select * from "Calls" WHERE
( ( "DateOfCall"='06/19/02') )
AND ( ( "CallType" = 'LONG DIST' ))
[DateOfCall is a DateTime field]
it takes 15 minutes!
both columns are indexed individually and expalain reports and Index scan for both
queries.
Any pointers where to look next?
Running pg 7.2 on RH
Thanks,
-Aaron Held
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Performance w/ multiple WHERE clauses
On Wed, 18 Sep 2002, Aaron Held wrote:
> I am running into a serious performance issue with some basic queries.
>
> If I run something like
>
>select * from "Calls" WHERE
> ( ("CallType" = 'LONG DIST' ))
>
> The search takes about 15 seconds
>
> if I run
> select * from "Calls" WHERE
> ( ( "DateOfCall"='06/19/02') )
> AND ( ( "CallType" = 'LONG DIST' ))
>[DateOfCall is a DateTime field]
>
> it takes 15 minutes!
>
> both columns are indexed individually and expalain reports and Index scan for both
> queries.
>
> Any pointers where to look next?
Are they using the same index? Have you done analyze
recently?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Index usage on date feild , differences in '>' and '>=' and between
Hi , I am trying to improve a query to use existing indexes but facing diffculty. Looks like 'between' amd '>=' are not using indexes althoug > and < does. all my application code uses between and i am sure it use to work fine at one point of time. regds mallah. SQL TRANSCRIPT: == tradein_clients=> explain select list_id from eyp_rfi a where generated > '2002-08-13' and generated < '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 rows=4150 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated >= '2002-08-13' and generated < '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated >= '2002-08-13' and generated <= '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-08-13' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) EXPLAIN tradein_clients=> == -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index usage on date feild , differences in '>' and '>='
On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote: > I am trying to improve a query to use existing indexes but facing diffculty. > > > Looks like 'between' amd '>=' are not using indexes althoug > and < does. > all my application code uses between and i am sure it use to work fine > at one point of time. > > > regds > mallah. > > SQL TRANSCRIPT: > >== > > tradein_clients=> explain select list_id from eyp_rfi a where generated > >'2002-08-13' and generated < '2002-09-19' ; > NOTICE: QUERY PLAN: > > Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 rows=4150 width=4) > > EXPLAIN > tradein_clients=> explain select list_id from eyp_rfi a where generated >= >'2002-08-13' and generated < '2002-09-19' ; > NOTICE: QUERY PLAN: > > Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) Given how close the two results are (and the fact that they differ by 900 rows), have you tried using set enable_seqscan=off and seeing what explain gives you for the second query? My guess is that it'll have an estimated cost greater than the 17923.81 it's estimating from the sequence scan. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Index usage on date feild , differences in '>' and '>=' and between
Thanks very much for the response. set enable_seqscan=off; Definitely helps. and for wide date ranges it usees indexes. But with default value of enable_sequence changing date range seems to have effect. can you explain me a bit more or point me to right documents for understanding the languae of "EXPLAIN". EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-11' and '2002-09-19' ; NOTICE: QUERY PLAN: Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=12924 width=4) EXPLAIN tradein_clients=> explain select list_id from eyp_rfi a where generated between '2002-09-12' and '2002-09-19' ; NOTICE: QUERY PLAN: Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..17369.05 rows=12220 width=4) EXPLAIN tradein_clients=> == the distribution of values are as follows: > select generated ,count(generated) from eyp_rfi a where generated between >'2002-09-10' and '2002-09-19' group by generated; generated | count +--- 2002-09-10 | 442 2002-09-11 | 1060 2002-09-12 | 641 2002-09-13 | 607 2002-09-14 | 1320 2002-09-15 | 521 2002-09-16 | 1474 2002-09-17 | 940 2002-09-18 | 1005 2002-09-19 | 178 (10 rows) Last Question , Shud i do "enable_seqscan=off" in Application Level? I use Perl-DBI Thanks and Regards Rajesh Mallah. Tsday 19 September 2002 12:07, Stephan Szabo wrote: > On Thu, 19 Sep 2002, Rajesh Kumar Mallah. wrote: > > I am trying to improve a query to use existing indexes but facing > > diffculty. > > > > > > Looks like 'between' amd '>=' are not using indexes althoug > and < does. > > all my application code uses between and i am sure it use to work fine > > at one point of time. > > > > > > regds > > mallah. > > > > SQL TRANSCRIPT: > > = > >= > > > > tradein_clients=> explain select list_id from eyp_rfi a where > > generated > '2002-08-13' and generated < '2002-09-19' ; NOTICE: > > QUERY PLAN: > > > > Index Scan using eyp_rfi_date on eyp_rfi a (cost=0.00..15796.97 > > rows=4150 width=4) > > > > EXPLAIN > > tradein_clients=> explain select list_id from eyp_rfi a where > > generated >= '2002-08-13' and generated < '2002-09-19' ; NOTICE: > > QUERY PLAN: > > > > Seq Scan on eyp_rfi a (cost=0.00..17923.81 rows=5061 width=4) > > Given how close the two results are (and the fact that they differ by 900 > rows), have you tried using set enable_seqscan=off and seeing what > explain gives you for the second query? My guess is that it'll have > an estimated cost greater than the 17923.81 it's estimating from the > sequence scan. -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
