Re: [SQL] Getting rid of accents..
Have a look at translate(). It behaves like the unix command 'tr'. Randall Lucas wrote: > > Hi Mallah, > > I had this problem once, and put together this bunch of regexes. It's > by no means optimal, but should solve 90% and would easily be adapted > into a plperl function. > > Begin perl: >$value =~ s/[\xc0-\xc6]/A/g; >$value =~ s/[\xc7]/C/g; >$value =~ s/[\xc8-\xcb]/E/g; >$value =~ s/[\xcc-\xcf]/I/g; >$value =~ s/[\xd1]/N/g; >$value =~ s/[\xd2-\xd6\xd8]/O/g; >$value =~ s/[\xd9-\xdc]/U/g; >$value =~ s/[\xdd]/Y/g; > >$value =~ s/[\xe0-\xe6]/a/g; >$value =~ s/[\xe7]/c/g; >$value =~ s/[\xe8-\xeb]/e/g; >$value =~ s/[\xec-\xef]/i/g; >$value =~ s/[\xf1]/n/g; >$value =~ s/[\xf2-\xf6\xd8]/o/g; >$value =~ s/[\xf9-\xfc]/u/g; >$value =~ s/[\xfd\xff]/y/g; > > On Tuesday, May 27, 2003, at 04:55 PM, <[EMAIL PROTECTED]> wrote: > > > > > > > Is there any easy way for converting accented text to > > closest text without accents in postgresql ? > > > > eg: > > > > BÂLÂ MORGHÂB to BALA MORGHAB > > > > > > > > > > Regds > > Mallah. > > > > > > - > > Get your free web based email at trade-india.com. > >"India's Leading B2B eMarketplace.!" > > http://www.trade-india.com/ > > > > > > > > ---(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 > > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] [ADMIN] Perl Book
On Tue, 2003-05-27 at 10:30, Jodi Kanter wrote: > I also need a Perl programming book to use as a reference but also as a > means to learning the product. I am a DBA but fairly new to coding in > Perl. > Does anyone have a recommendation? > Thanks > Jodi > You might want to give "PostgreSQL" by Korry Douglas (Sam's Publishing) a spin, as it has information regarding the internals of postgresql, database administration, and several chapters on different interfaces to postgresql (inlcuding about 50 pages on perl and dbi). Also, not sure how relevant it is, but oreilly has a number of bio-informatics books, several of which deal specifically with perl. Robert Treat ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] schema-qualified permission problem
Why can't my primary user (ttvuser) access tables (owned by owneruser) for
which they've been granted access? I can describe the table, but can't
read it.
===
Here's what I'm getting:
ttvdev=> \c - owneruser
Password:
You are now connected as new user owneruser.
ttvdev=> \d
List of relations
Schema | Name | Type | Owner
++--+
owneruser | users | table| owneruser
ttvdev=> select count(*) from owneruser.users;
count
---
0
(1 row)
ttvdev=> \dp
Access privileges for database "ttvdev"
Schema | Table |Access privileges
++-
owneruser | users | {=,owneruser=arwdRxt,ttvuser=arwd}
ttvdev=> \c - postgres
Password:
You are now connected as new user postgres.
ttvdev=# select count(*) from owneruser.users;
count
---
0
(1 row)
ttvdev=> \c - ttvuser
Password:
You are now connected as new user ttvuser.
ttvdev=> \d owneruser.users
Table "owneruser.users"
Column |Type | Modifiers
+-+---
user_id| integer | not null
initials | character varying(3)| not null
username | character varying(18) | not null
password | character varying(25) | not null
email | character varying(256) | not null
authenticationdate | timestamp without time zone |
creationdate | timestamp without time zone |
modifydate | timestamp without time zone |
userlastmodified | timestamp without time zone |
adminlastmodified | timestamp without time zone |
autologin | character varying(1)|
active | character varying(1)|
passhint | character varying(25) |
firstname | character varying(40) |
lastname | character varying(40) |
sex| character varying(6)|
department | character varying(40) |
manager_flag | character varying(1)|
phone | character varying(50) |
Indexes: pk_users primary key btree (user_id)
ttvdev=> select count(*) from owneruser.users;
ERROR: owneruser: permission denied
---(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] recursive srf
Working on my first set returning function... So far the examples from
http://techdocs.postgresql.org/guides/SetReturningFunctions have worked well
for me...
I'd like to see what kind of performance I get from a particularly slow
piece of code by replacing it with a recursive srf (right now, I do the
recursion in php).
So, here's my working example, I haven't bench marked it yet, but if someone
would look at it and tell me if there's any improvements that can be made,
I'd appreciate it. My first impression is that it's fast, because it
appeared to have returned instantaneously. I really don't understand the
"explain analyze" output, but I'm including it as well.
I'd love to get some feedback on this (did I say that already?).
Imagine this:
CREATE TYPE nav_list AS (id int8, accountid varchar(12),
...snip... , parent int8, subfolders int8);
subfolders is the count() of records that have their parent set to this
record's id. I want to take a list of something like this:
home
- item 1
- item 2
- sub item 1
- item 3
and return it so that it comes out in this order
home
item1
item2
sub item 1
item 3
create or replace function nav_srf(varchar(12), int8) returns setof nav_list
as '
DECLARE
r nav_list%rowtype;
depth int8;
last_id int8;
records RECORD;
BEGIN
FOR r IN SELECT * FROM navigation WHERE accountid = $1 AND parent =
$2 ORDER BY dsply_order LOOP
depth := r.subfolders;
last_id := r.id;
RETURN NEXT r;
IF depth > 0 THEN
FOR records IN SELECT * FROM nav_srf($1, last_id)
LOOOP
RETURN NEXT records;
END LOOP;
END IF;
END LOOP;
RETURN;
END
' LANGUAGE 'plpgsql';
# EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS00', 0);
QUERY PLAN
Function Scan on nav_srf (cost=0.00..12.50 rows=1000 width=134) (actual
time=85.78..86.19 rows=22 loops=1)
Total runtime: 86.37 msec
(2 rows)
I then ran it again a moment later and got:
# EXPLAIN ANALYZE SELECT * FROM nav_srf('GOTDNS00', 0);
QUERY PLAN
Function Scan on nav_srf (cost=0.00..12.50 rows=1000 width=134) (actual
time=23.54..23.97 rows=22 loops=1)
Total runtime: 24.15 msec
(2 rows)
BTW, this started out as a question about how to do it, but in the process
of thinking my question out, the answer came to me. ;-)
Matthew Nuzum
www.bearfruit.org
[EMAIL PROTECTED]
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] schema-qualified permission problem
Charlie Toohey <[EMAIL PROTECTED]> writes: > Why can't my primary user (ttvuser) access tables (owned by owneruser) for > which they've been granted access? I can describe the table, but can't > read it. > ttvdev=> select count(*) from owneruser.users; > ERROR: owneruser: permission denied You didn't grant USAGE permission on the owneruser schema. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] The problems about temporary tables in V7.3.2
Hi All A view is created with a normal table xx without a schema specified. And before using the view, a temporary table xx , which has the same name as the normal table xx used by the view. In the same connection session, it supposes the view would read the temporary table. But actually it always reads the normal table instead of temporary table. The same thing happens to the table specified with a schema. If a SELECT statement uses a table with a schema specified, the SELECT statement won't read the temporary table when it exists with the same name. I just wonder the schema feature just removes the access priority of the temporary tables. Jack ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] The problems about temporary tables in V7.3.2
"jack" <[EMAIL PROTECTED]> writes: > A view is created with a normal table xx without a schema specified. And > before using the view, a temporary table xx , which has the same name as the > normal table xx used by the view. In the same connection session, it > supposes the view would read the temporary table. But actually it always > reads the normal table instead of temporary table. The same thing happens to > the table specified with a schema. If a SELECT statement uses a table with a > schema specified, the SELECT statement won't read the temporary table when > it exists with the same name. I just wonder the schema feature just removes > the access priority of the temporary tables. This is all the intended behavior. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] How to undelete deleted data
How can I undelete the deleted data from a table; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] How To Recover Deleted Data
I've accidentally deleted the data in my table. So How to recover it back. ---(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] How to undelete deleted data
On Thu, May 29, 2003 at 17:37:19 +0800, Abdul Wahab Dahalan <[EMAIL PROTECTED]> wrote: > How can I undelete the deleted data from a table; If you notice before you commit, you issue a rollback. Otherwise you need to recover from backup. If you don't have a backup, then you might be able to do some fancy stuff to see old tuples. If you are going to do this you should shutdown the database and make a copy of what you have. If you haven't done a vacuum since the delete, the old tuples should still be stored in the database. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
