[SQL] how many tuples on a cursor?
Hello, I want to know how many tuples there on a cursor, and i found http://groups.google.de/groups?hl=de&lr=&threadm=3ACA7BB0.7020106%402cactus.com&rnum=7&prev=/groups%3Fhl%3Dde%26lr%3D%26q%3Dpostgres%2Bcursor%2Bcount%26btnG%3DSuche But, this posting is very old (2001-04-03), i'm using 7.4.6. Is there now a way to get the total number of rows in a CURSOR? Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] trigger/rule question
Enrico Weigelt wrote: > > * Christoph Haller <[EMAIL PROTECTED]> wrote: > > Hi, > > > I assume this still refers to > > [SQL] RULE for mtime recording > > from last Friday. > > ehm, yeah. I forgot that I've already asked this stuff ... > hmmpf. seems I'm not getting younger ;-) > > > > I gave it another thought and > > I am now having something which seems to work. > > The trick is interpose a view to avoid the > > rule recursion: > > > > correct me if I'm wrong: > > you dont let the application write to the actual storage table, but > instead to a view, which a modified write to the actual storage, where > also the reads get their data from. Exactly. > > okay, that's really an idea worth to think about :) > > insert should work the same way. but how to implement delete ? > (the application should only see one table, so in our case the view). Exactly. > if we user "DO INSTEAD", we wont get anything to delete (AFAIK), so > we cannot intercept here. the only chance seems to leave out "INSTEAD" > and live with duplicate data. No. What's wrong with (referring to my previous post) CREATE OR REPLACE RULE joo_delete AS ON DELETE TO joo_view DO INSTEAD DELETE FROM joo WHERE bar = OLD.bar ; DELETE FROM joo_view WHERE bar = '...' ; works perfectly for me Or did I miss something here? Regards, Christoph > > Did I miss anyting ? > > cu > -- > - > Enrico Weigelt== metux IT service > phone: +49 36207 519931 www: http://www.metux.de/ > fax: +49 36207 519932 email: [EMAIL PROTECTED] > - > Realtime Forex/Stock Exchange trading powered by postgresSQL :)) > http://www.fxignal.net/ > - > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > >http://archives.postgresql.org ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] how many tuples on a cursor?
Andreas Kretschmer wrote: > > Hello, > > I want to know how many tuples there on a cursor, and i found > http://groups.google.de/groups?hl=de&lr=&threadm=3ACA7BB0.7020106%402cactus.com&rnum=7&prev=/groups%3Fhl%3Dde%26lr%3D%26q%3Dpostgres%2Bcursor%2Bcount%26btnG%3DSuche > > But, this posting is very old (2001-04-03), i'm using 7.4.6. > > Is there now a way to get the total number of rows in a CURSOR? > > Andreas > -- Assuming you are using the libpq interface, after FETCH ALL you'll get the number of rows by PQntuples(). What else seems to work is MOVE 2147483647 (INT_MAX) and then get the max number of rows by PQcmdTuples(). Back to the beginning by FETCH ABSOLUTE 0. But I have no idea how expensive this MOVE is. Regards, Christoph ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] how many tuples on a cursor?
Christoph Haller <[EMAIL PROTECTED]> writes: > What else seems to work is MOVE 2147483647 > (INT_MAX) and then get the max number of rows > by PQcmdTuples(). I'd suggest "MOVE FORWARD ALL" rather than hard-wiring assumptions about the maximum possible value of infinity ;-) > But I have no idea how expensive this MOVE is. The rows are all calculated internally --- about all you save compared to a FETCH is data formatting and transmission. If you really have to know this number in advance of fetching the data, that's pretty much what you have to do, but it will cost you ... regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] how many tuples on a cursor?
Tom Lane wrote: > > Christoph Haller <[EMAIL PROTECTED]> writes: > > What else seems to work is MOVE 2147483647 > > (INT_MAX) and then get the max number of rows > > by PQcmdTuples(). > > I'd suggest "MOVE FORWARD ALL" rather than hard-wiring assumptions > about the maximum possible value of infinity ;-) > Of course. I should have read the manual on this more carefully. Regards, Christoph ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] Query two database at once
Hi, I want to ewecute a query on two database like this : SELECT * FROM table01 T1, table02 T2 WHERE T1.gid=T2.gid AND T2.my_field='".$value."' The problem is that table01 is in a different database than table02 and i don't know how to telle postgresql to look in two db. Is there a solution other than execute two query ? Thanks ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] Java and Currval
I'm having trouble getting the currval function to work from a Java program (or from Postgres at all). I want to get the value of addressid in the table taddress. The best I seem to be able to come up with is 'SELECT currval(taddress.addressid)'. However this gives me the error 'ERROR: relation "1" does not exist' or whatever the number should be. I've tried lots of variations but can't seem to work it out. What am I doing wrong? Thanks. -Sam ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] Java and Currval
On Mon, May 02, 2005 at 05:30:12PM +0100, Sam Adams wrote:
>
> I'm having trouble getting the currval function to work from a Java
> program (or from Postgres at all). I want to get the value of addressid
> in the table taddress. The best I seem to be able to come up with is
> 'SELECT currval(taddress.addressid)'.
The argument to currval() is a quoted sequence name, so you probably
need something like this:
SELECT currval('taddress_addressid_seq');
In PostgreSQL 8.0 you can use pg_get_serial_sequence() to get the
sequence name from the table and column names:
SELECT currval(pg_get_serial_sequence('taddress', 'addressid'));
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Java and Currval
Hi,
On 5/2/05, Sam Adams <[EMAIL PROTECTED]> wrote:
> I'm having trouble getting the currval function to work from a Java
> program (or from Postgres at all). I want to get the value of addressid
> in the table taddress. The best I seem to be able to come up with is
> 'SELECT currval(taddress.addressid)'. However this gives me the error
> 'ERROR: relation "1" does not exist' or whatever the number should be.
> I've tried lots of variations but can't seem to work it out. What am I
> doing wrong?
You should use the sequence as parameter to currval(), like:
=> \d products
Table "public.products"
-[ RECORD 1 ]--
Column| proid
Type | integer
Modifiers | not null default nextval('public.products_proid_seq'::text)
=> SELECT currval(products.proid);
ERROR: relation "1" does not exist
=> SELECT currval('public.products_proid_seq'::text);
nextval
-
7
(1 row)
Regards.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Java and Currval
On Mon, May 02, 2005 at 17:30:12 +0100,
Sam Adams <[EMAIL PROTECTED]> wrote:
> I'm having trouble getting the currval function to work from a Java
> program (or from Postgres at all). I want to get the value of addressid
> in the table taddress. The best I seem to be able to come up with is
> 'SELECT currval(taddress.addressid)'. However this gives me the error
> 'ERROR: relation "1" does not exist' or whatever the number should be.
> I've tried lots of variations but can't seem to work it out. What am I
> doing wrong? Thanks.
I don't know what JAVA brings to the mix, but the argument to currval
is a string. Before version 8 you would do something like:
SELECT currval('taddress_addressid_seq')
This string isn't guaranteed to work, but would normally be the correct
one. In 8.0 there is a function that will return the sequence name.
Note that you must have also done a nextval or setval call on that
sequence in the current session or the value is undefined and trying to
access it will result in an error.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [despammed] [SQL] Query two database at once
am 02.05.2005, um 17:36:05 +0200 mailte Stéphane RIFF folgendes: > Hi, > > I want to ewecute a query on two database like this : > > SELECT * FROM table01 T1, table02 T2 WHERE T1.gid=T2.gid AND > T2.my_field='".$value."' > > The problem is that table01 is in a different database than table02 and i > don't know how to telle postgresql > to look in two db. > Is there a solution other than execute two query ? The answer is: yes and no. No: PG can't connect to two databases and do a select across. Yes: take a look at the contrib on dblink. You should look in the archiv of the list, Tom Lane and other people described the way with dblink in the past. Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] odd error
I am getting an odd error. Same data loaded on windows does not give it and even going from a client over VLAN does not give it, so I am guessing it is based on timing or something. Microsoft Cursor Engine error '80004005' Data provider or other service returned an E_FAIL status. /app/searchlist.asp, line 1113 Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. © 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments.
[SQL] Record Log Trigger
Hi all, I am building a database in postgresql and I made a function that returns the system time and the current user... like this: CREATE OR REPLACE FUNCTION generate_idx() returns text as $$ select to_char(now(),'MMDDHHMISSUSTZ')||CURRENT_USER; $$ language 'SQL'; CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS $$ BEGIN NEW.idxm = generate_idx(); RETURN NEW; END; $$ LANGUAGE plpgsql; And my all tables have the "idxm" field, its something like a log for the record, to know Who and When the record have changed. I.e: CREATE TABLE products( id serial primary key, description varchar(50), ... idxm varchar(100) ); CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products FOR EACH ROW EXECUTE PROCEDURE TG_idxm(); Okay, it runs fine... but my question is: Is it right??? In the future (when the database will be bigger with many of millions records) this functions for each table will depreceate my database performance??? Is there any other way to build it??? Thank you ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Record Log Trigger
One change you might want to look at is not using the now() function. According to the docs, the now() function always returns the start of the transaction time. So, if your code is using transaction blocks, the time may not be what you are expecting. This is what I had do to in my trigger to get the current clock time: to_char(to_timestamp(timeofday(),\'Dy Mon DD HH24:MI:SS.US \') HTH, Chris --( Forwarded letter 1 follows )- Date: Mon, 02 May 2005 16:10:46 -0300 To: [EMAIL PROTECTED] From: [EMAIL PROTECTED] Sender: [EMAIL PROTECTED] Subject: [SQL] Record Log Trigger Hi all, I am building a database in postgresql and I made a function that returns the system time and the current user... like this: CREATE OR REPLACE FUNCTION generate_idx() returns text as $$ select to_char(now(),'MMDDHHMISSUSTZ')||CURRENT_USER; $$ language 'SQL'; CREATE OR REPLACE FUNCTION TG_idxm() RETURNS trigger AS $$ BEGIN NEW.idxm = generate_idx(); RETURN NEW; END; $$ LANGUAGE plpgsql; And my all tables have the "idxm" field, its something like a log for the record, to know Who and When the record have changed. I.e: CREATE TABLE products( id serial primary key, description varchar(50), ... idxm varchar(100) ); CREATE TRIGGER TG_products_idxm BEFORE INSERT or UPDATE on products FOR EACH ROW EXECUTE PROCEDURE TG_idxm(); Okay, it runs fine... but my question is: Is it right??? In the future (when the database will be bigger with many of millions records) this functions for each table will depreceate my database performance??? Is there any other way to build it??? Thank you ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Function or Field?
Hi. What is the better way to store the last record for a translation??? I.E: The data for the last product vendding. What is better: a) Create a field in "product" table and create a Trigger (before insert or update into vendding table) to alter this field. b) Create a view or function that check the all venddings (in vendding table) for the specified product and return the last vendding information? a) CREATE TABLE products( id serial primary key, description varchar(50), last_vendding date()--Is correct to use this field??? ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE TRIGGER TG_change_products_last_vendding_field on table vendding BEFORE INSERT OR UPDATE FOR EACH ROW EXECUTE procedure change_products_last_vendding(); b) CREATE TABLE products ( id serial primary key, description varchar(50) ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE VIEW last_product_change as SELECT * from vendding order by date_ desc limit 1; --Okay, this view will return the last record and not the last record for a product... but its a example. I am asking it becouse I have used CLIPPER(dbase) for my old programs and in DBASE the view/check function that will check for each select is not functional. And I need to create a field in all table references, but in DBASE this fields allways broken and I need to recheck it. Thank you. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Function or Field?
You could also make a table with just that data in it so you don't have the field in all the records and you don't have to check all the records to see what is next. I am assuming this is some kind of a flag values used in a batch, if you just need the last id I use max(id)+1. Joel Fradkin Wazagua, Inc. 2520 Trailmate Dr Sarasota, Florida 34243 Tel. 941-753-7111 ext 305 [EMAIL PROTECTED] www.wazagua.com Powered by Wazagua Providing you with the latest Web-based technology & advanced tools. C 2004. WAZAGUA, Inc. All rights reserved. WAZAGUA, Inc This email message is for the use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and delete and destroy all copies of the original message, including attachments. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] Sent: Monday, May 02, 2005 3:17 PM To: pgsql-sql@postgresql.org Subject: [SQL] Function or Field? Hi. What is the better way to store the last record for a translation??? I.E: The data for the last product vendding. What is better: a) Create a field in "product" table and create a Trigger (before insert or update into vendding table) to alter this field. b) Create a view or function that check the all venddings (in vendding table) for the specified product and return the last vendding information? a) CREATE TABLE products( id serial primary key, description varchar(50), last_vendding date()--Is correct to use this field??? ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE TRIGGER TG_change_products_last_vendding_field on table vendding BEFORE INSERT OR UPDATE FOR EACH ROW EXECUTE procedure change_products_last_vendding(); b) CREATE TABLE products ( id serial primary key, description varchar(50) ); CREATE TABLE vendding( id serial primary key, date_ date, product integer references (products) ); CREATE VIEW last_product_change as SELECT * from vendding order by date_ desc limit 1; --Okay, this view will return the last record and not the last record for a product... but its a example. I am asking it becouse I have used CLIPPER(dbase) for my old programs and in DBASE the view/check function that will check for each select is not functional. And I need to create a field in all table references, but in DBASE this fields allways broken and I need to recheck it. Thank you. ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(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] [ANNOUNCE] pgtop, display PostgreSQL processes in `top' style
Hi, Cosimo, Cosimo Streppone wrote: > 1) is it possible to know Pg backend uptime with >SQL queries? Or must I look at postmaster.pid file? >or even something else? In contrib, there's a function caled backend_pid() defined in misc_utils.sql, it may be helpful for you. markus signature.asc Description: OpenPGP digital signature
[SQL] Trimming the cost of ORDER BY in a simple query
Hello,
I have a simple query with a pretty high cost (EXPLAIN ...), and I'm
wondering if I can somehow trim it.
Query (shows the last 7 dates):
=> SELECT DISTINCT date_part('year', uu.add_date), date_part('month',
uu.add_date), date_part('day', uu.add_date) FROM user_url uu WHERE
uu.user_id=1 ORDER BY date_part('year', uu.add_date) DESC,
date_part('month', uu.add_date) DESC, date_part('day', uu.add_date)
DESC LIMIT 7;
QUERY PLAN:
-
Limit (cost=4510.14..4522.93 rows=2 width=8) (actual
time=19.924..20.160 rows=7 loops=1)
-> Unique (cost=4510.14..4522.93 rows=2 width=8) (actual
time=19.919..20.139 rows=7 loops=1)
-> Sort (cost=4510.14..4513.34 rows=1279 width=8) (actual
time=19.915..20.004 rows=78 loops=1)
Sort Key: date_part('year'::text, add_date),
date_part('month'::text, add_date), date_part('day'::text, add_date)
-> Index Scan using foo on user_url uu
(cost=0.00...14 rows=1279 width=8) (actual time=0.095..14.761
rows=1225 loops=1)
Index Cond: (user_id = 1)
Total runtime: 20.313 ms
(7 rows)
It looks like the cost is all in ORDER BY, and if I remove ORDER BY the
execution time goes from 20-90 ms to less than 1 ms.
I do need the 7 most recent add_dates. Is there a more efficient way
of grabbing them?
Thanks,
Otis
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Trimming the cost of ORDER BY in a simple query
Quoting [EMAIL PROTECTED]:
> I have a simple query with a pretty high cost (EXPLAIN ...), and I'm
> wondering if I can somehow trim it.
>
> Query (shows the last 7 dates):
>
> => SELECT DISTINCT date_part('year', uu.add_date),
> date_part('month',
> uu.add_date), date_part('day', uu.add_date) FROM user_url uu
> WHERE
> uu.user_id=1 ORDER BY date_part('year', uu.add_date) DESC,
> date_part('month', uu.add_date) DESC, date_part('day',
> uu.add_date)
> DESC LIMIT 7;
>
> QUERY PLAN:
>
-
> Limit (cost=4510.14..4522.93 rows=2 width=8) (actual
> time=19.924..20.160 rows=7 loops=1)
>-> Unique (cost=4510.14..4522.93 rows=2 width=8) (actual
> time=19.919..20.139 rows=7 loops=1)
> -> Sort (cost=4510.14..4513.34 rows=1279 width=8) (actual
> time=19.915..20.004 rows=78 loops=1)
>Sort Key: date_part('year'::text, add_date),
> date_part('month'::text, add_date), date_part('day'::text, add_date)
>-> Index Scan using foo on user_url uu
> (cost=0.00...14 rows=1279 width=8) (actual time=0.095..14.761
> rows=1225 loops=1)
> Index Cond: (user_id = 1)
> Total runtime: 20.313 ms
> (7 rows)
>
>
> It looks like the cost is all in ORDER BY, and if I remove ORDER BY
> the
> execution time goes from 20-90 ms to less than 1 ms.
>
> I do need the 7 most recent add_dates. Is there a more efficient
> way
> of grabbing them?
The query analyzer is using the sort to detect and return distinct
values, as well. So there's not much point in trying to remove it.
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
