Re: [SQL] simple problem???
ivan marchesini wrote: Dear users It's a simple problem I think (and I hope :-) I have a table like this: A|B|C 1|2|20 1|3|10 1|4|21 2|3|12 2|4|22 3|4|23 where the first two column are a combination without repetition (with k=2) of the numbers 1,2,3,4 for each pair I have a value in the column C. I would want a select that can extract these records: 1|3|10 2|3|12 3|4|23 i.e. the select must look into the A field first and: -select all records where A=1, -find, into this selection, the record where there's the minimum value of the field C -print all the fields for this record. then -select all records where A=2 -find, into this selection, the record where there's the minimum value of the field C -print all the fields of this record. and so on... using SELECT a,MIN(c) FROM table GROUP BY a is a partial solution because I can't see the value of B and I obtain: 1|10 2|12 3|23 How can I do for plotting also the value of B??? Thank you very much Ivan Maybe you could try Select distinct on (a,b) a,b,c from(select * from table order by A,C) The distinct on construct is a postgreSql-ism Cordialement -- Patrick ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Help with privilages please
Tom Thank you - I think that the underlying problem is that I was trying out REVOKE ALL FROM TABLE suppliers FOR public; then connect as hilary and I can still see the table rows. I appear to have to revoke each type eg REVOKE SELECT FROM TABLE suppliers FOR public; etc and then the code works. Is this a known bug in this version (7.4.1)? Thanks Hilary At 18:08 20/07/2006 -0400, Tom Lane wrote: Hilary Forbes <[EMAIL PROTECTED]> writes: > I have an existing table suppliers and I have created a new user > 'hilary' > REVOKE ALL on TABLE suppliers FROM hilary; > now login as hilary > SELECT * from suppliers; > and I get all the records!!! Most likely there's been a grant of (at least) select privilege to PUBLIC. You'll need to revoke that if you don't want every user to have that privilege implicitly. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend Hilary Forbes DMR Limited (UK registration 01134804) A DMR Information and Technology Group company (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **
[SQL] System catalog table privileges
Dear All Next question on privileges! Can I safely remove all privileges from the system catalog tables for a user and still enable them to select from the public schema? I guess the real question is what access rights does an ordinary user have to have to the system catalog tables in order for postgres to work properly given I only ever want the user to be able to SELECT from views. This is all brought about by a user who wants to use MS Access Query for adhoc queries to a (small) database via ODBC. (the database itself drives a major web application.) I can't find an easy way of preventing them seeing that tables exist but I don't want them trying to manually update any tables of mine or postgres's thank you very much! (Don't shoot the messenger - there's no accounting for user's tastes!) TAI Hilary Hilary Forbes DMR Limited (UK registration 01134804) A DMR Information and Technology Group company (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **
Re: [SQL] System catalog table privileges
On 7/21/06, Hilary Forbes <[EMAIL PROTECTED]> wrote: Dear All Next question on privileges! Can I safely remove all privileges from the system catalog tables for a user and still enable them to select from the public schema? I guess the real question is what access rights does an ordinary user have to have to the system catalog tables in order for postgres to work properly given I only ever want the user to be able to SELECT from views. This is all brought about by a user who wants to use MS Access Query for adhoc queries to a (small) database via ODBC. (the database itself drives a major web application.) I can't find an easy way of preventing them seeing that tables exist but I don't want them trying to manually update any tables of mine or postgres's thank you very much! (Don't shoot the messenger - there's no accounting for user's tastes!) This doesn't address the permissions issue but is a suggestion regarding your approach on granting access to an untrusted user for reporting purposes... Whenever I have a user that needs to do reporting from any production database, I set up a separate reporting database. If possible, this is placed on a completely different machine and the data is fed from production to the reporting server nightly. Tech savy business users (the ones who typically need this kind of access) are notorious for writing bad queries and causing performance problems. If you isolate their activity, you will eliminate lots of headache. If they cause a problem on the reporting server, you don't have to drop everything to get the problem fixed like you would if they caused problems on the live database. An argument that the users who run the reports often make is that they need the most current data. Most of the time this is not the case. My recommendation is to let the users create the queries they need to run for realtime data on the reporting database, then pass them by an expert for review before putting them into an IT controlled reporting application. Bottom line, be careful about giving non-experts too much access to your live production data.== Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com==
Re: [SQL] Error when trying to use a FOR loop
Many thanks to Stephan, Richard and George. When I was reading the documentation about FOR loops, I didn't realize that I was in the plpgsql section! CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ BEGIN DECLARE lv RECORD; FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP SELECT ldev FROM ldevrg WHERE ldevrg='$lv'; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; So I've made progress, but I'm running into the same thing. psql:rgio.sql:16: ERROR: syntax error at or near "FOR" at character 86 psql:rgio.sql:16: LINE 6: FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP psql:rgio.sql:16: ^ If I comment out the DECLARE statement above, I get the following: psql:rgio.sql:16: ERROR: loop variable of loop over rows must be record or row variable at or near "LOOP" at character 129 psql:rgio.sql:16: LINE 6: FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP psql:rgio.sql:16: ^ Ideas? From: [EMAIL PROTECTED] on behalf of Kevin Nikiforuk Sent: Thu 7/20/2006 7:46 AM To: pgsql-sql@postgresql.org Subject: [SQL] Error when trying to use a FOR loop Sorry if this is in the archives, but I've done a search and couldn't find anything relevant. I'm running HP's precompiled version of 8.1.3.1 as part of their Internet Express offering, and I can't seem to run a for loop. Here's what I'm seeing: xp512-0715-0716=# FOR LV in 1..10 LOOP xp512-0715-0716-# select * from ldevrg; ERROR: syntax error at or near "FOR" at character 1 LINE 1: FOR LV in 1..10 LOOP ^ I'm still pretty new to postgres, but based on the documentation I'm not picking up what I'm doing wrong. Help much appreciated. Thanks, Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Error when trying to use a FOR loop
am 21.07.2006, um 9:26:21 -0600 mailte Kevin Nikiforuk folgendes: > Many thanks to Stephan, Richard and George. When I was reading the > documentation about FOR loops, I didn't realize that I was in the plpgsql > section! > > CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ > BEGIN > DECLARE lv RECORD; lv is a record... > FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP untested: tv.rg HTH, Andreas -- Andreas Kretschmer(Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net ===Schollglas Unternehmensgruppe=== ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Error when trying to use a FOR loop
"Kevin Nikiforuk" <[EMAIL PROTECTED]> writes: > CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ > BEGIN > DECLARE lv RECORD; > FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP > SELECT ldev FROM ldevrg WHERE ldevrg='$lv'; > END LOOP; > RETURN 1; > END; > $$ LANGUAGE plpgsql; You've got a small error in the layout: the DECLARE part goes before BEGIN not after. Swap the first two lines of the function. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] System catalog table privileges
Hilary Forbes <[EMAIL PROTECTED]> writes: > Next question on privileges! Can I safely remove all privileges > from the system catalog tables for a user Various people have experimented with doing that, but it tends to break a lot of stuff. I suggest you just revoke privileges on the tables you don't want the users messing with, and not get too worried about whether they know the tables exist or not. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Error when trying to use a FOR loop
On Fri, 21 Jul 2006, Kevin Nikiforuk wrote: > Many thanks to Stephan, Richard and George. When I was reading the > documentation about FOR loops, I didn't realize that I was in the plpgsql > section! > > CREATE OR REPLACE FUNCTION rgio() RETURNS integer as $$ > BEGIN > DECLARE lv RECORD; You'd want to put declare first (it goes before begin). > FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP > SELECT ldev FROM ldevrg WHERE ldevrg='$lv'; I think you'd want something like ldevrg=lv.rg. > END LOOP; What's the final intent for this since AFAICS this is just going to do busy work that throws away the results. If you wanted to see the results of each of these selects you have to do a bit more work. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] System catalog table privileges
Aaron Thanks for this one - I had actually wondered about doing that but the trouble is that they say that they need up to the minute reports not "as of last night". Indeed, I do have another app where I do just that because I find that reports indexes/requirements are very different to transactional type requirements. However, you have made me make up my mind to see if I can persuade them to work on data that is a day old. What we really need is a good graphical (Windows based) query/report tool that allows us to configure the tables to be viewed etc etc and, most importantly, is license free. There's fame for someone there Hilary At 10:07 21/07/2006 -0500, Aaron Bono wrote: On 7/21/06, Hilary Forbes <[EMAIL PROTECTED]> wrote: Dear All Next question on privileges! Can I safely remove all privileges from the system catalog tables for a user and still enable them to select from the public schema? I guess the real question is what access rights does an ordinary user have to have to the system catalog tables in order for postgres to work properly given I only ever want the user to be able to SELECT from views. This is all brought about by a user who wants to use MS Access Query for adhoc queries to a (small) database via ODBC. (the database itself drives a major web application.) I can't find an easy way of preventing them seeing that tables exist but I don't want them trying to manually update any tables of mine or postgres's thank you very much! (Don't shoot the messenger - there's no accounting for user's tastes!) This doesn't address the permissions issue but is a suggestion regarding your approach on granting access to an untrusted user for reporting purposes... Whenever I have a user that needs to do reporting from any production database, I set up a separate reporting database. If possible, this is placed on a completely different machine and the data is fed from production to the reporting server nightly. Tech savy business users (the ones who typically need this kind of access) are notorious for writing bad queries and causing performance problems. If you isolate their activity, you will eliminate lots of headache. If they cause a problem on the reporting server, you don't have to drop everything to get the problem fixed like you would if they caused problems on the live database. An argument that the users who run the reports often make is that they need the most current data. Most of the time this is not the case. My recommendation is to let the users create the queries they need to run for realtime data on the reporting database, then pass them by an expert for review before putting them into an IT controlled reporting application. Bottom line, be careful about giving non-experts too much access to your live production data. == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com == Hilary Forbes DMR Limited (UK registration 01134804) A DMR Information and Technology Group company (www.dmr.co.uk) Direct tel 01689 889950 Fax 01689 860330 DMR is a UK registered trade mark of DMR Limited **
Re: [SQL] System catalog table privileges
On Fri, 2006-07-21 at 11:19, Hilary Forbes wrote: > Aaron > > Thanks for this one - I had actually wondered about doing that but the > trouble is that they say that they need up to the minute reports not > "as of last night". Indeed, I do have another app where I do just > that because I find that reports indexes/requirements are very > different to transactional type requirements. However, you have made > me make up my mind to see if I can persuade them to work on data that > is a day old. > > What we really need is a good graphical (Windows based) query/report > tool that allows us to configure the tables to be viewed etc etc and, > most importantly, is license free. There's fame for someone > there A couple of points. 1: You can still use a slave server, just look into slony. It's what we use, and it's quite reassuring to know that I can let Joe Sixpack loose with a query editor and not worry about him killing my main production database. 2: If you HAVE to let an untrusted user have access to your real database, then make them use views, and place some kind of limit in the view that keeps them from blowing things up with really ugly queries. That won't stop a truly determined person from DOSing your server, but will help. 3: Look at pentaho. It's got a standalone java version that will do a lot in the graphical (windowsish) realm. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] CREATE TABLE AS inside of a function
So now that I've got my loops working, on to my next newbie question. I've created my function and in it, I want to loop through the results of a select and for each value of my loop counter, I want to create a new table, but I can't figure out how to use a variable in the name of the new table, see below. CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP CREATE TABLE rgio_$lv AS SELECT ldev FROM ldevrg WHERE rg='$lv'; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; Thanks, Kevin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] CREATE TABLE AS inside of a function
On 7/21/06, Kevin Nikiforuk <[EMAIL PROTECTED]> wrote: So now that I've got my loops working, on to my next newbie question. I've created my function and in it, I want to loop through the results of a select and for each value of my loop counter, I want to create a new table, but I can't figure out how to use a variable in the name of the new table, see below. CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP CREATE TABLE rgio_$lv AS SELECT ldev FROM ldevrg WHERE rg='$lv'; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; Thanks, Kevin See: http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Regards, Rodrigo ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] CREATE TABLE AS inside of a function
Rodrigo De Leon wrote: On 7/21/06, Kevin Nikiforuk <[EMAIL PROTECTED]> wrote: So now that I've got my loops working, on to my next newbie question. I've created my function and in it, I want to loop through the results of a select and for each value of my loop counter, I want to create a new table, but I can't figure out how to use a variable in the name of the new table, see below. CREATE OR REPLACE FUNCTION rgio() RETURNS INTEGER as $$ DECLARE lv RECORD; BEGIN FOR lv IN SELECT DISTINCT rg FROM ldevrg LOOP CREATE TABLE rgio_$lv AS SELECT ldev FROM ldevrg WHERE rg='$lv'; END LOOP; RETURN 1; END; $$ LANGUAGE plpgsql; Thanks, Kevin See: http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN Also, I really recommend enough that you read chapters 32. Extending SQL, 33. Triggers, and 36. Pl/pgSQL in their entirety. In fact, to keep up with the linking to them for you: http://www.postgresql.org/docs/8.1/interactive/extend.html http://www.postgresql.org/docs/8.1/interactive/triggers.html http://www.postgresql.org/docs/8.1/interactive/plpgsql.html And, so that I don't feel like I'm becoming one of those rtfm jerks I always complain about: what you need to do is place your CREATE TABLE statement in an EXECUTE directive like so (inside the the FOR body): EXECUTE 'CREATE TABLE rgio_' || $lv || ' AS SELECT ldev FROM ldevrg WHERE rg=\'' || $lv || '\';' -- this line could have also been: WHERE rg=' || quote_literal($lv) || ';' EXECUTE takes a query in a string to execute and you need to use string concatenation to build the string if you're using variables from the function in the query. Pl/pgSQL doesn't have any variable substitution inside of strings (like in double quoted string in PHP) which is why you need to use the concatenation bit. -- erik jones <[EMAIL PROTECTED]> software development emma(r) ---(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
