Re: [SQL] Permissons on database
> "bk" == Boulat Khakimov <[EMAIL PROTECTED]> writes:
bk> How do I grant permissions on everything in the selected
bk> databes?
bk> GRANT doesnt take as on object database name nor does it
bk> accept wild chars
Attached is some Perl code I wrote long ago to do this. This
particular code was done for Keystone, a problem tracking database and
it would do a "GRANT ALL". Modify it as needed. Last I checked it
worked with both PostgreSQL 6.5.x and 7.0.x
#! /usr/bin/perl -w
# I'm also appending a Perl script to grant public access to all
# keystone tables. It uses the Pg module for PostgreSQL, so you will
# need to add that first. However, I find it a bit less tedious than
# granting access by hand
# Roland B. Roberts, PhD Custom Software Solutions
# [EMAIL PROTECTED] 101 West 15th St #4NN
# [EMAIL PROTECTED] New York, NY 10011
use Pg;
if (defined $ARGV[0]) {
$dbname = $ARGV[0];
} else {
$dbname = "keystone";
}
print "connecting to $dbname\n";
$dbh = Pg::connectdb("dbname=$dbname $ARGV[1]");
die "Pg::connectdb failed, $dbh->errorMessage"
unless ($dbh->status == PGRES_CONNECTION_OK);
$c{relname} = $dbh->exec ("select relname from pg_class where relname !~ '^pg_' and
relkind != 'i'");
die "Pg::exec, $dbh->errorMessage"
unless ($c{relname}->resultStatus == PGRES_TUPLES_OK);
for ($i = 0; $i < $c{relname}->ntuples; $i++) {
$relname = $c{relname}->getvalue($i,0);
print "grant all on $relname to public\n";
$c{grant} = $dbh->exec ("grant all on $relname to public");
die "Pg::exec, ".$dbh->errorMessage
unless ($c{grant}->resultStatus == PGRES_COMMAND_OK);
}
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED] Forest Hills, NY 11375
---(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] my pgsql error?
> "Tom" == Tom Lane <[EMAIL PROTECTED]> writes: >> For a moment I thought you could do to_char(now(),'Day DD Month ' >> but you're quite right you need to rtrim() the various pieces. Tom> regression=# select to_char(now()-10,'Day DD Month '); Tom>to_char Tom> - Tom> Sunday04 March 2001 Tom> (1 row) Tom> regression=# select to_char(now()-10,'fmDay fmDD fmMonth '); Tom>to_char Tom> - Tom> Sunday 4 March 2001 Tom> (1 row) Tom> The 'fm' prefix is not very well explained in the docs at It doesn't seem to be explained at all, just listed in the table. I'd suggest adding the following (and perhaps include your example selects from above) to the usage notes below table 4-11: o to_char() does not remove trailing blanks from fields which are blank padded, e.g., `Day' and `Month', unless the `FM' prefix is used. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] LIMIT within UNION?
> "Andrew" == Andrew Perrin <[EMAIL PROTECTED]> writes: Andrew> What I've done is to write a script that counts the number Andrew> of eligible exposure candidates: Andrew> SELECT count(*) FROM participants WHERE Andrew> AND typenr=1 Andrew> Then subtract that number (currently 28) from 200 to get Andrew> 172 control participants. Then the problem starts. [...] Andrew> returns ERROR: parser: parse error at or near "ORDER" Can you do this via a subselect: SELECT * FROM ( SELECT ... FROM participants WHERE typenr=1 AND UNION SELECT ... FROM participants WHERE typenr=2 LIMIT 172 ) ORDER BY zip; roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] How to select and result row number??
> "Yudie" == Yudie <[EMAIL PROTECTED]> writes:
Yudie> what is the select statement so I can get result records like this
Yudie> no cityname
Yudie> --
Yudie> 1 NEW YORK
Yudie> 2 LOS ANGELES
Yudie> 3 HOUSTON
Yudie> 4
Yudie> 5 ...
This is not really a SQL type result; you are probably better of
writing something in a procedural language. That said, something like
this seems to work for 7.2:
create sequence temp_counter;
select nextval('temp_counter'), * from whatever;
If you have an 'order by', the above will not work. You could then
try either building a temporary table or using a subselect
select nextval('temp_counter'), * from (select order by ...);
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED] Forest Hills, NY 11375
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
> "Martijn" == Martijn van Oosterhout <[EMAIL PROTECTED]> writes: Martijn> Well, what I would suggest is that when you wrap several Martijn> statements into a single transaction with begin/commit, Martijn> the whole lot could be considered a single statement Martijn> (since they form an atomic transaction so in a sense they Martijn> are all executed simultaneously). And hence Postgresql is Martijn> perfectly compliant. FWIW, and not that I am an Oracle fan :-), Oracle seems to interpret this the same way when using a "select sysdate from dual" inside a transaction. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
> "Ross" == Ross J Reedstrom <[EMAIL PROTECTED]> writes: Ross> Oh, interesting datapoint. Let me get this clear - on Ross> oracle, the equivalent of: Well, I've never gone off to lunch in the middle, but in Oracle 7, I had transactions which definitely took as much as a few minutes to complete where the timestamp on every row committed was the same. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] CURRENT_TIMESTAMP
SQL> create table rbr_foo (a date); Table created. SQL> begin 2 insert into rbr_foo select sysdate from dual; [...wait about 10 seconds...] 3 insert into rbr_foo select sysdate from dual; 4 end; 5 / PL/SQL procedure successfully completed. SQL> select * from rbr_foo; A - SEP 27, 2002 12:57:27 SEP 27, 2002 12:57:27 Note that, as near as I can tell, Oracle 8 does NOT have timestamp or current_timestamp. Online docs say both are present in Oracle 9i. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Passing array to PL/SQL and looping
> "Greg" == Greg Johnson <[EMAIL PROTECTED]> writes:
Greg> CREATE FUNCTION test_array( ) RETURNS VARCHAR[] AS '
Greg> DECLARE
Greg> return_array VARCHAR[];
Greg> BEGIN
Greg> return_array[0] := ''test'';
Greg> return_array[1] := ''test 1'';
Greg> return_array[2] := ''test 2'';
Greg> RETURN (return_array);
Greg> END;'
Greg> LANGUAGE 'plpgsql';
Greg> I get the following error when I try to run it:
Greg> ERROR: parse error at or near "[" on line 4.
I raised this issue when dealing with version 7.1 and the conclusion
was that PL/PgSQL doesn't understand array syntax. If you construct
the array as { val, val, val, ... } it works, but that is not a
practical solution for most uses.
What can we do to at least get this on the radar screen as a known
bug?
roland
--
PGP Key ID: 66 BC 3B CD
Roland B. Roberts, PhD RL Enterprises
[EMAIL PROTECTED] 76-15 113th Street, Apt 3B
[EMAIL PROTECTED] Forest Hills, NY 11375
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [GENERAL] arrays
> "Josh" == Josh Berkus <[EMAIL PROTECTED]> writes: Josh> Now, I know at least one person who is using arrays to store Josh> scientific data. However, that data arrives in his lab in Josh> the form of matrices, and is not used for joins or query Josh> criteria beyond a simple "where" clause. Indeed, my first attempt to use arrays was to maintain some basic statistics about a set of data. The array elements where to be distribution moments and would only be used in "where" clauses. The problem was that I wanted to be about to update the statistics using triggers whenever the main data was updated. The inability to access a specific array element in PL/pgSQL code made this so painful I ended up just extending a table with more columns. roland -- PGP Key ID: 66 BC 3B CD Roland B. Roberts, PhD RL Enterprises [EMAIL PROTECTED] 76-15 113th Street, Apt 3B [EMAIL PROTECTED] Forest Hills, NY 11375 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
