Re: [SQL] [PHP] [ADMIN] Data insert
On Sun, Aug 21, 2005 at 06:35:22AM +0100, Aldor wrote: > if you want to insert biiig data volumes try either using COPY instead > of INSERT - it will run much much faster And if for some reason you have to stick with inserts, group them into transactions; it will perform much better than individual transactions. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Softwarehttp://pervasive.com512-569-9461 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] SQL CASE Statements
Dear Lane, is that what you want? CREATE TABLE network_nodes ( node_id SERIAL PRIMARY KEY, node_name VARCHAR, default_gateway_interface_id INTEGER ); CREATE TABLE router_interfaces ( interface_id SERIAL PRIMARY KEY, node_id INT REFERENCES network_nodes ); CREATE VIEW current_default_gateways_v (router_id, default_gateway) AS SELECT interface_id, CASE WHEN interface_id IN (SELECT interface_id FROM router_interfaces ri, network_nodes nn WHERE ri.node_id = nn.node_id AND ri.interface_id = nn.default_gateway_interface_id) THEN 1 ELSE 0 END AS if_default_gateway FROM router_interfaces; INSERT INTO network_nodes VALUES(DEFAULT, 'node1',1); INSERT INTO network_nodes VALUES(DEFAULT, 'node2',2); INSERT INTO network_nodes VALUES(DEFAULT, 'node3',3); INSERT INTO network_nodes VALUES(DEFAULT, 'node4',4); INSERT INTO router_interfaces VALUES(DEFAULT,1); INSERT INTO router_interfaces VALUES(DEFAULT,2); INSERT INTO router_interfaces VALUES(DEFAULT,2); INSERT INTO router_interfaces VALUES(DEFAULT,1); SELECT * FROM network_nodes; SELECT * FROM router_interfaces; SELECT * FROM current_default_gateways_v; teste=> SELECT * FROM network_nodes; node_id | node_name | default_gateway_interface_id -+---+-- 1 | node1 |1 2 | node2 |2 3 | node3 |3 4 | node4 |4 (4 rows) teste=> SELECT * FROM router_interfaces; interface_id | node_id --+- 1 | 1 2 | 2 3 | 2 4 | 1 (4 rows) teste=> SELECT * FROM current_default_gateways_v; router_id | default_gateway ---+- 1 | 1 2 | 1 3 | 0 4 | 0 (4 rows) --- Lane Van Ingen <[EMAIL PROTECTED]> escreveu: > Halley, here is a sample for you that might help; the purpose of this > function was to set an indicator of '1' or '0' (true or false) on a router > interface if the router interface ID was the same as the default gateway for > the Router node ID: > > create view current_default_gateways_v (router_id, default_gateway) AS > select router_id, > case > when router_id in (select interface_id from router_interface ri, > network_nodes nn > where ri.node_id = nn.node_id > and ri.interface_id = nn.default_gateway_interface_id) > then 1 > else 0 > end as if_default_gateway > from router_interface; > > TABLES USED: > network_nodes: > node_id, serial > node_name, varchar > default_gateway_interface_id, integer > > router_interfaces: > interface_id, serial (integer) > node_id (FK) > __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PL/SQL Function: self-contained transaction?
I think the Enterprise DB folks are actively working it. I don't know what their plans to release their work back to the community are. Mail thread: http://archives.postgresql.org/pgsql-general/2005-08/msg00582.php Article: http://oetrends.com/news.php?action=view_record&idnum=428 Home: http://www.enterprisedb.com Rick [EMAIL PROTECTED] wrote on 08/22/2005 01:20:00 PM: > > "Marc G. Fournier" <[EMAIL PROTECTED]> writes: > > > In PostgreSQL, as everyone knows, a QUERY == a transaction, unless > wrap'd in a > > BEGIN/END explicitly ... how does that work with a function? is there an > > implicit BEGIN/END around the whole transaction, or each QUERY within the > > function itself? > > The whole outer query issued from your frontend is in one transaction. > > > If the whole function (and all QUERYs inside of it) are considered one > > transaction, can you do a begin/end within the function itself to 'force' > > commit on a specific part of the function? > > Functions cannot issue start or end transactions. They're a creature of the > transaction you're in when you call them. Otherwise it wouldn't make sense to > be able to call them from within a query. > > There is some discussion of "stored procedures" which would live outside of > transactions and be able to create transactions, commit, and roll them back. > But I don't think any of that work is committed yet. I'm not even sure it's > been written yet. > > -- > greg > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Why Doesn't SQL This Expression Work?
Thanks again, Dmitri. I put a round() function around it, and got exactly what I was looking for. Tried a cast earlier, but put it in the wrong place! :-( Just noted that the manual (version 8.0, section 9.3) warns about integer arithmetic and truncation of results, but fortunately there is a way around it! Thanks again -Original Message- From: Dmitri Bichko [mailto:[EMAIL PROTECTED] Sent: Monday, August 22, 2005 4:42 PM To: Lane Van Ingen; pgsql-sql@postgresql.org Subject: RE: [SQL] Why Doesn't SQL This Expression Work? I believe the problem is that the expression is being eavluated as an integer, so it's rounded down before it's multiplied by 100; A simple cast to float4 should help: test=> select (589824 / ((240 * 255840) / 8) * 100); ?column? -- 0 (1 row) test=> select (589824 / ((240 * 255840)::float4 / 8) * 100); ?column? -- 7.68480300187617 (1 row) Dmitri > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van Ingen > Sent: Monday, August 22, 2005 3:51 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Why Doesn't SQL This Expression Work? > > > Hi, am trying to do a simple computation on two views, but > for some reason the current_util_in computation always > returns zero. All fields being used are integer. > > select a.if_id, > a.in_count, > a.time_incr, > b.speed, > ((a.time_incr * b.speed) / 8) as possible_bytes, > (a.in_count / ((a.time_incr * b.speed) / 8) * 100) AS > current_util_in, > from if_history_view1 a, speed_history_view1 b > where a.if_id = b.if_id > and a.if_id = 2; > > The inner computation (a.time_incr * b.speed / 8) evaluated > properly to 7675200. Add the "in_count divide operation", and > the result is zero. > > The result expected is a percentage, and should compute to > 7.68 (8 as an integer), when multiplied by 100. What is wrong > here? Here is the result: > Row if_id in_count time_incr speedpossible_bytes > current_util_in >12 589824240255840 7675200 0 > > > > ---(end of > broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why Doesn't SQL This Expression Work?
On Mon, 22 Aug 2005, Lane Van Ingen wrote: > Hi, am trying to do a simple computation on two views, but for some reason > the current_util_in computation always returns zero. All fields being used > are integer. > > select a.if_id, > a.in_count, > a.time_incr, > b.speed, > ((a.time_incr * b.speed) / 8) as possible_bytes, > (a.in_count / ((a.time_incr * b.speed) / 8) * 100) AS current_util_in, > from if_history_view1 a, speed_history_view1 b > where a.if_id = b.if_id > and a.if_id = 2; > > The inner computation (a.time_incr * b.speed / 8) evaluated properly to > 7675200. > Add the "in_count divide operation", and the result is zero. Integer division doesn't follow all the same rules as normal division would. In particular (a/b)*c is not the same as a*c/b. Also, I think you may be expecting rounding rather than truncation (and in the case of (a.time_incr*b.speed)/8 can that not be a multiple of 8, and if so what should happen?) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Why Doesn't SQL This Expression Work?
I believe the problem is that the expression is being eavluated as an integer, so it's rounded down before it's multiplied by 100; A simple cast to float4 should help: test=> select (589824 / ((240 * 255840) / 8) * 100); ?column? -- 0 (1 row) test=> select (589824 / ((240 * 255840)::float4 / 8) * 100); ?column? -- 7.68480300187617 (1 row) Dmitri > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Lane Van Ingen > Sent: Monday, August 22, 2005 3:51 PM > To: pgsql-sql@postgresql.org > Subject: [SQL] Why Doesn't SQL This Expression Work? > > > Hi, am trying to do a simple computation on two views, but > for some reason the current_util_in computation always > returns zero. All fields being used are integer. > > select a.if_id, > a.in_count, > a.time_incr, > b.speed, > ((a.time_incr * b.speed) / 8) as possible_bytes, > (a.in_count / ((a.time_incr * b.speed) / 8) * 100) AS > current_util_in, > from if_history_view1 a, speed_history_view1 b > where a.if_id = b.if_id > and a.if_id = 2; > > The inner computation (a.time_incr * b.speed / 8) evaluated > properly to 7675200. Add the "in_count divide operation", and > the result is zero. > > The result expected is a percentage, and should compute to > 7.68 (8 as an integer), when multiplied by 100. What is wrong > here? Here is the result: > Row if_id in_count time_incr speedpossible_bytes > current_util_in >12 589824240255840 7675200 0 > > > > ---(end of > broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Why Doesn't SQL This Expression Work?
Hi, am trying to do a simple computation on two views, but for some reason the current_util_in computation always returns zero. All fields being used are integer. select a.if_id, a.in_count, a.time_incr, b.speed, ((a.time_incr * b.speed) / 8) as possible_bytes, (a.in_count / ((a.time_incr * b.speed) / 8) * 100) AS current_util_in, from if_history_view1 a, speed_history_view1 b where a.if_id = b.if_id and a.if_id = 2; The inner computation (a.time_incr * b.speed / 8) evaluated properly to 7675200. Add the "in_count divide operation", and the result is zero. The result expected is a percentage, and should compute to 7.68 (8 as an integer), when multiplied by 100. What is wrong here? Here is the result: Row if_id in_count time_incr speedpossible_bytes current_util_in 12 589824240255840 7675200 0 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] PL/SQL Function: self-contained transaction?
am 22.08.2005, um 14:16:30 -0300 mailte Marc G. Fournier folgendes: > > In PostgreSQL, as everyone knows, a QUERY == a transaction, unless wrap'd > in a BEGIN/END explicitly ... how does that work with a function? is there > an implicit BEGIN/END around the whole transaction, or each QUERY within > the function itself? > > If the whole function (and all QUERYs inside of it) are considered one > transaction, Yes, exactly. Regards, 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 2: Don't 'kill -9' the postmaster
Re: [SQL] PL/SQL Function: self-contained transaction?
"Marc G. Fournier" <[EMAIL PROTECTED]> writes: > In PostgreSQL, as everyone knows, a QUERY == a transaction, unless wrap'd in a > BEGIN/END explicitly ... how does that work with a function? is there an > implicit BEGIN/END around the whole transaction, or each QUERY within the > function itself? The whole outer query issued from your frontend is in one transaction. > If the whole function (and all QUERYs inside of it) are considered one > transaction, can you do a begin/end within the function itself to 'force' > commit on a specific part of the function? Functions cannot issue start or end transactions. They're a creature of the transaction you're in when you call them. Otherwise it wouldn't make sense to be able to call them from within a query. There is some discussion of "stored procedures" which would live outside of transactions and be able to create transactions, commit, and roll them back. But I don't think any of that work is committed yet. I'm not even sure it's been written yet. -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
[SQL] PL/SQL Function: self-contained transaction?
In PostgreSQL, as everyone knows, a QUERY == a transaction, unless wrap'd in a BEGIN/END explicitly ... how does that work with a function? is there an implicit BEGIN/END around the whole transaction, or each QUERY within the function itself? If the whole function (and all QUERYs inside of it) are considered one transaction, can you do a begin/end within the function itself to 'force' commit on a specific part of the function? Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] A Table's Primary Key Listing
On Mon, Aug 22, 2005 at 03:23:29AM -0700, Roger Tannous wrote: > So, D'Arcy's solution, although described as 'unsatisfactory' (ref.: > D'Arcy's message), seem to be the only solution. > > So I noticed I was trying to play the wise man, trying to do things in a > better way, but nothing was found than D'Arcy's query: There's a PL/pgSQL function, which was posted to the spanish list: http://archives.postgresql.org/pgsql-es-ayuda/2005-08/msg00644.php Not sure if it qualifies as "better" or "worse" for you. -- Alvaro Herrera () "El que vive para el futuro es un iluso, y el que vive para el pasado, un imbécil" (Luis Adler, "Los tripulantes de la noche") ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem calling stored procedure
On Mon, 22 Aug 2005 [EMAIL PROTECTED] wrote: > Hi all, > > I've written a stored procedure but am having trouble calling it. > > The procedure name is called "insert_period" and I am calling using: > > SELECT > insert_period(1::INTEGER,'2005-09-13'::DATE,'2005-09-15'::DATE,'unavailable_periods'); > > But am getting the error message: > > - > > ERROR: syntax error at or near "$1" at character 70 > QUERY: SELECT * FROM bookings WHERE (start_date, end_date) OVERLAPS > (DATE $1 - interval '1 day', DATE $2 + interval '1 day') AND property_id > = $3 LIMIT 1 > CONTEXT: PL/pgSQL function "insert_period" line 12 at select into variables > -- > > I've used EMS PostgreSQL Manager to write the function, and have > successfully used the debugger to step through the function using > various calling arguments without issue - I only get this problem when > trying to call the function through a client. > > Research on this revealed problems when variable names are named after > existing postgres functions/tables/columns, but I to my knowledge there > is nothing in the database named the same of my arguments. I've tried > renaming them all to random names, but to no avail. I've also tried > declaring the variables as ALIAS FOR in the DECLARE section, but again > no luck. The other thing that concerns me is that the error shows $1 > being used as a DATE argument, I would have thought 'prop_id' (See > below) would have been $1? Me too, however in any case, DATE is for date literals so I don't believe it's what you want in this case anyway since you're using a variable. I think you'd just want new_start_date, etc, since they're already dates. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Problem calling stored procedure
On Mon, Aug 22, 2005 at 03:17:02PM +0200, [EMAIL PROTECTED] wrote: > ERROR: syntax error at or near "$1" at character 70 > QUERY: SELECT * FROM bookings WHERE (start_date, end_date) OVERLAPS (DATE $1 > - interval '1 day', DATE $2 + interval '1 day') AND property_id = $3 LIMIT 1 > CONTEXT: PL/pgSQL function "insert_period" line 12 at select into variables > SELECT INTO clashes * FROM bookings WHERE (start_date, end_date) > OVERLAPS (DATE new_start_date - interval '1 day', DATE new_end_date + > interval '1 day') AND property_id = prop_id LIMIT 1; Why did you write "DATE new_start_date" and "DATE new_end_date"? That's not the correct syntax for casting, and those variables are already of type DATE anyway. > The other thing that concerns me is that the error shows $1 being > used as a DATE argument, I would have thought 'prop_id' (See below) > would have been $1? $N in the error message refers to a statement preparation argument, not to a function argument. For insight into what PL/pgSQL is doing, see the PREPARE documentation: http://www.postgresql.org/docs/8.0/static/sql-prepare.html -- Michael Fuhr ---(end of broadcast)--- TIP 1: 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] SQL CASE Statements
Halley, here is a sample for you that might help; the purpose of this function was to set an indicator of '1' or '0' (true or false) on a router interface if the router interface ID was the same as the default gateway for the Router node ID: create view current_default_gateways_v (router_id, default_gateway) AS select router_id, case when router_id in (select interface_id from router_interface ri, network_nodes nn where ri.node_id = nn.node_id and ri.interface_id = nn.default_gateway_interface_id) then 1 else 0 end as if_default_gateway from router_interface; TABLES USED: network_nodes: node_id, serial node_name, varchar default_gateway_interface_id, integer router_interfaces: interface_id, serial (integer) node_id (FK) -Original Message- From: Halley Pacheco de Oliveira [mailto:[EMAIL PROTECTED] Sent: Saturday, August 20, 2005 7:25 AM To: pgsql-sql@postgresql.org Cc: [EMAIL PROTECTED] Subject: RE: SQL CASE Statements > Has anybody done this? If so, can you send me a sample? CREATE TEMPORARY TABLE fruits (id SERIAL, name TEXT); INSERT INTO fruits VALUES (DEFAULT, 'banana'); INSERT INTO fruits VALUES (DEFAULT, 'apple'); CREATE TEMPORARY TABLE food (id SERIAL, name TEXT); INSERT INTO food VALUES (DEFAULT, 'apple'); INSERT INTO food VALUES (DEFAULT, 'spinach'); SELECT name, CASE WHEN name = ANY (SELECT name FROM fruits) THEN 'yes' ELSE 'no' END AS fruit FROM food; name | fruit -+--- apple | yes spinach | no (2 lines) __ Converse com seus amigos em tempo real com o Yahoo! Messenger http://br.download.yahoo.com/messenger/ ---(end of broadcast)--- TIP 1: 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] Problem calling stored procedure
Hi all, I've written a stored procedure but am having trouble calling it. The procedure name is called "insert_period" and I am calling using: SELECT insert_period(1::INTEGER,'2005-09-13'::DATE,'2005-09-15'::DATE,'unavailable_periods'); But am getting the error message: - ERROR: syntax error at or near "$1" at character 70 QUERY: SELECT * FROM bookings WHERE (start_date, end_date) OVERLAPS (DATE $1 - interval '1 day', DATE $2 + interval '1 day') AND property_id = $3 LIMIT 1 CONTEXT: PL/pgSQL function "insert_period" line 12 at select into variables -- I've used EMS PostgreSQL Manager to write the function, and have successfully used the debugger to step through the function using various calling arguments without issue - I only get this problem when trying to call the function through a client. Research on this revealed problems when variable names are named after existing postgres functions/tables/columns, but I to my knowledge there is nothing in the database named the same of my arguments. I've tried renaming them all to random names, but to no avail. I've also tried declaring the variables as ALIAS FOR in the DECLARE section, but again no luck. The other thing that concerns me is that the error shows $1 being used as a DATE argument, I would have thought 'prop_id' (See below) would have been $1? I have included the function below - Anyone have any ideas? Cheers, Neil. - CREATE OR REPLACE FUNCTION "public"."insert_period" (prop_id integer, new_start_date date, new_end_date date, into_table varchar) RETURNS integer AS $body$ DECLARE cur_overlap refcursor; new_id INTEGER; num_entries INTEGER; row_one record; row_two record; clashes record; BEGIN LOCK TABLE calendar_entries IN EXCLUSIVE MODE; SELECT INTO clashes * FROM bookings WHERE (start_date, end_date) OVERLAPS (DATE new_start_date - interval '1 day', DATE new_end_date + interval '1 day') AND property_id = prop_id LIMIT 1; IF NOT FOUND THEN DELETE FROM calendar_entries WHERE property_id = prop_id AND (start_date >= new_start_date) AND (end_date <= new_end_date); OPEN cur_overlap FOR SELECT *, pg_class.relname AS table FROM calendar_entries WHERE (start_date, end_date) OVERLAPS (new_start_date - interval '2 days', new_end_date + interval '2 days') AND property_id = prop_id AND pg_class.oid = tableoid ORDER BY start_date; GET DIAGNOSTICS num_entries = ROW_COUNT; IF (num_entries = 1) THEN /* We're overlapping one row. Either we're enveloped by a single row, or we have one row overlapping either the start date or the end date. */ FETCH cur_overlap INTO row_one; IF (row_one.start_date <= new_start_date) AND (row_one.end_date >= new_end_date) THEN /* We're enveloped. The enveloping row needs to be split in to two so that we can insert ourselves. */ IF row_one.table = into_table THEN /* This period is already marked appropriately. Do nothing. */ ELSE /* We need to perform a split/insert. 1. Adjust the end date of the enveloping row to the new start - 1 day. 2. Insert a new row as the same type as the enveloping row from new_end_date + 1 to the existing end date. 3. Insert the new row in to the required table */ EXECUTE 'UPDATE ' || row_one.table || ' SET end_date = DATE ''' || new_start_date || ''' - interval ''1 day'' WHERE id = ' || row_one.id; EXECUTE 'INSERT INTO ' || row_one.table || ' (start_date, end_date) VALUES (DATE ''' || new_end_date || ''' + interval ''1 day'', DATE ''' || row_one.end_date || ''')'; EXECUTE 'INSERT INTO ' || into_table || ' (start_date, end_date) VALUES (DATE ''' || new_start_date || ''', DATE ''' || new_end_date || ''')'; END IF; ELSIF row_one.start_date <= new_start_date THEN /* This row is earlier than the proposed period - It's overlapping our start date - But is it of the same type? */ IF row_one.table = into_table THEN /* A single row overlapping the start only and of the same type - Update the end date and return the existing row ID */ EXECUTE 'UPDATE ' || into_table || ' SET end_date = ' || DATE || || new_end_date || ''' WHERE id = ' || row_one.id; RETURN row_one.id; ELSE /* Single row, overlapping the start, and of a different type. Trim back the existing row and Insert and return newly
Re: [SQL] A Table's Primary Key Listing
So, D'Arcy's solution, although described as 'unsatisfactory' (ref.: D'Arcy's message), seem to be the only solution. So I noticed I was trying to play the wise man, trying to do things in a better way, but nothing was found than D'Arcy's query: SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname FROM pg_class JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND pg_namespace.nspname NOT LIKE 'pg_%' AND pg_class.relname like 'sip_%' JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND pg_attribute.attisdropped='f' JOIN pg_index ON pg_index.indrelid=pg_class.oid AND pg_index.indisprimary='t' AND ( pg_index.indkey[0]=pg_attribute.attnum OR pg_index.indkey[1]=pg_attribute.attnum OR pg_index.indkey[2]=pg_attribute.attnum OR pg_index.indkey[3]=pg_attribute.attnum OR pg_index.indkey[4]=pg_attribute.attnum OR pg_index.indkey[5]=pg_attribute.attnum OR pg_index.indkey[6]=pg_attribute.attnum OR pg_index.indkey[7]=pg_attribute.attnum OR pg_index.indkey[8]=pg_attribute.attnum OR pg_index.indkey[9]=pg_attribute.attnum ) ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname; Regards, Roger Tannous. --- "D'Arcy J.M. Cain" wrote: > On Thu, 18 Aug 2005 09:40:57 -0700 (PDT) > Roger Tannous <[EMAIL PROTECTED]> wrote: > > Thanks for your query :) > > > > But it only shows the first of the primary keys of tables having > multiple > > primary keys :) > > > > This is apparently because of the pg_index.indkey[0] thing, so how can > we > > manage this query in order to get all of the keys :) > > That's a good question. The following query does this in a very > unsatisfactory way. Anyone know what the general solution would be? > > SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname > FROM pg_class > JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND > pg_namespace.nspname NOT LIKE 'pg_%' > JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND > pg_attribute.attisdropped='f' > JOIN pg_index ON pg_index.indrelid=pg_class.oid AND > pg_index.indisprimary='t' AND > ( > pg_index.indkey[0]=pg_attribute.attnum OR > pg_index.indkey[1]=pg_attribute.attnum OR > pg_index.indkey[2]=pg_attribute.attnum OR > pg_index.indkey[3]=pg_attribute.attnum OR > pg_index.indkey[4]=pg_attribute.attnum OR > pg_index.indkey[5]=pg_attribute.attnum OR > pg_index.indkey[6]=pg_attribute.attnum OR > pg_index.indkey[7]=pg_attribute.attnum OR > pg_index.indkey[8]=pg_attribute.attnum OR > pg_index.indkey[9]=pg_attribute.attnum > ) > ORDER BY pg_namespace.nspname, pg_class.relname,pg_attribute.attname; > > -- > D'Arcy J.M. Cain | Democracy is three wolves > http://www.druid.net/darcy/| and a sheep voting on > +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
