[SQL] referencing serials
Hello, which data type should be used to hold references to SERIALs in external tables? I tried to use SERIAL, but then a sequence is created for the referencing table. Markus
[SQL] test
test -- ** POZZO Ange Michel mail : [EMAIL PROTECTED] Administrateur - Développeur ALPINFO 617 Rue Denis Papin 73290 La Motte Servolex Savoie - France tel : 04 79 26 06 28 fax : 04 79 25 68 36 Zonecommerce, l'annuaire français du commerce électronique - Plusieurs centaines de magasin référencé pour tous vos achats sur internet, tous avec paiement sécurisé en ligne - Des promotions proposées par les boutiques - Vente au enchères, forum de discussion - Des actualités, sports et loisirs, cinéma, horoscope ... - Les iddées d'olivia, le site du mois, l'interview - Des liens, des conseils ... http://www.zonecommerce.com/ **
Re: [SQL] test
At least you could spell your ads correctly ! - Plusieurs centaines de magasins re'fe'rence's - Les ide'es d'olivia bye, Jerome ALET - [EMAIL PROTECTED] - http://cortex.unice.fr/~jerome Faculte de Medecine de Nice - http://noe.unice.fr - Tel: 04 93 37 76 30 28 Avenue de Valombrose - 06107 NICE Cedex 2 - FRANCE On Fri, 21 Jul 2000, Ange Michel POZZO wrote: > test > > -- > ** > POZZO Ange Michel > mail : [EMAIL PROTECTED] > Administrateur - Développeur > ALPINFO > 617 Rue Denis Papin > 73290 La Motte Servolex > Savoie - France > tel : 04 79 26 06 28 > fax : 04 79 25 68 36 > > Zonecommerce, l'annuaire français du commerce électronique > > - Plusieurs centaines de magasin référencé pour tous vos achats > sur internet, tous avec paiement sécurisé en ligne > - Des promotions proposées par les boutiques > - Vente au enchères, forum de discussion > - Des actualités, sports et loisirs, cinéma, horoscope ... > - Les iddées d'olivia, le site du mois, l'interview > - Des liens, des conseils ... > > http://www.zonecommerce.com/ > > ** > > >
Re: [SQL] problem with view and case - please help
> CREATE VIEW browser > AS > SELECT > agent_i, > CASE > WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0' > ... > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > ELSE agent_i END AS navigateur, count (agent_i) > as total from access group by agent_i; > pqReadData() -- backend closed the channel unexpectedly. > This probably means the backend terminated abnormally > before or while processing the request. > We have lost the connection to the backend, so further processing is > impossible. Terminating. Query limit exceeded? At least that CASE statement looks rather clumsy, suggest using a function instead. V.Paul
Re: [SQL] referencing serials
On 21 Jul 2000, at 9:41, Markus Wagner wrote: > which data type should be used to hold references to SERIALs in external > tables? integer I believe. Actually if you \d a table with a serial you'll see that it's an integer with DEFAULT clause specified.
[SQL] using OID as primary key
Hi, are there any disadvantages of using OID as the primary key for any table? What about referencing external tuples using their OIDs? e. g.: CREATE TABLE thistable ... field NUMERIC REFERENCES OtherTable (oid) Which data type should I use to reference OIDs? Will I run into problems later if I do my things this way? Thanks, Markus
Re: [SQL] using OID as primary key
Markus Wagner wrote: > Hi, > > are there any disadvantages of using OID as the primary key for any > table? > What about referencing external tuples using their OIDs? > > e. g.: > CREATE TABLE thistable > ... > field NUMERIC REFERENCES OtherTable (oid) > > Which data type should I use to reference OIDs? > > Will I run into problems later if I do my things this way? Currently OID is not supported for references. Has to do with some checks done in the RI triggers, using SPI functions. We're thinking about a solution... Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [SQL] using OID as primary key
Thus spake Jan Wieck > > are there any disadvantages of using OID as the primary key for any > > table? > > What about referencing external tuples using their OIDs? > > Currently OID is not supported for references. Has to do with > some checks done in the RI triggers, using SPI functions. > We're thinking about a solution... Also, don't forget about the dump/restore issue. You have to remember to dump OIDs with "-o" or else all your references will be trashed. Personally I would avoid it. Adding a serial type almost gives you the same thing anyway. OIDs are useful in system tables and temporarily in programs when dealing with tables without primary keys. -- 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.
[SQL] Timestamp indexes
A while back I as told (by Tom Lane I *think*) that timestamp (previously
datetime) fields couldn't be indexed as such and that I should index them
using this method :
CREATE INDEX "applicants_resubmitted" on "applicants" using btree ( date
("resubmitted") "date_ops" );
Since almost all the queries that search that field search it casting the
field to date, I thought that would be OK.. It was for a while (in the 6.5.X
days) but it seems that 7.0.2 is treating this different. I can't get an
index scan on that field no matter what I do.
Any suggestions?
Thanks!
-Mitch
Re: [SQL] problem with view and case - please help
Instead of: > WHEN $1 LIKE \'Mozilla/2.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape 2.0\' > WHEN $1 LIKE \'Mozilla/2.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape 2.02\' > WHEN $1 LIKE \'Mozilla/2.02E %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape 2.02E\' > WHEN $1 LIKE \'Mozilla/3.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape 3.0\' > WHEN $1 LIKE \'Mozilla/3.01 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape 3.01\' > WHEN $1 LIKE \'Mozilla/3.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/3.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.04 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.05 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.06 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.07 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.08 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.5 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.51 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.6 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.61 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.7 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.71 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.72 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/4.73 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/5.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' > WHEN $1 LIKE \'Mozilla/6.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT > LIKE \'%compatible%\' THEN \'Netscape\' Why not use: WHEN $1 LIKE \'Mozilla/%\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' It should be true based on the rest of your function. Cheers.
Re: [SQL] Timestamp indexes
"Mitch Vincent" <[EMAIL PROTECTED]> writes:
> A while back I as told (by Tom Lane I *think*) that timestamp (previously
> datetime) fields couldn't be indexed as such
That's certainly not true now, if it ever was...
regression=# create table applicants(resubmitted timestamp);
CREATE
regression=# create index applicants_i on applicants(resubmitted);
CREATE
regression=# explain select * from applicants where resubmitted = 'today';
NOTICE: QUERY PLAN:
Index Scan using applicants_i on applicants (cost=0.00..8.14 rows=10 width=8)
EXPLAIN
> and that I should index them
> using this method :
> CREATE INDEX "applicants_resubmitted" on "applicants" using btree ( date
> ("resubmitted") "date_ops" );
> Since almost all the queries that search that field search it casting the
> field to date, I thought that would be OK.. It was for a while (in the 6.5.X
> days) but it seems that 7.0.2 is treating this different. I can't get an
> index scan on that field no matter what I do.
Dunno, it works for me ...
regression=# CREATE INDEX "applicants_resubmitted" on "applicants"
regression-# (date("resubmitted") "date_ops" );
CREATE
regression=# explain select * from applicants where date(resubmitted) = 'today';
NOTICE: QUERY PLAN:
Index Scan using applicants_resubmitted on applicants (cost=0.00..8.16 rows=10
width=8)
EXPLAIN
regression=# explain select * from applicants where resubmitted::date = 'today';
NOTICE: QUERY PLAN:
Index Scan using applicants_resubmitted on applicants (cost=0.00..8.16 rows=10
width=8)
EXPLAIN
You would want an index on date() of the field if this is what most of
your queries look like --- a straight index on the timestamp isn't
useful for such a query. But I don't know why you're not getting
index scans. More details please?
regards, tom lane
Re: [SQL] Timestamp indexes
select * from applicants as a where (a.created::date > '05-01-2000' or a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 There is one of the queries.. I just remembered that the order by was added since last time I checked it's PLAN (in the 6.5.X days) -- could that be the problem? 8784 records in the applicant database. created and resubmitted are both timestamps. NOTICE: QUERY PLAN: Sort (cost=2011.65..2011.65 rows=4880 width=611) -> Seq Scan on applicants a (cost=0.00..1712.68 rows=4880 width=611) ProcessQuery ! system usage stats: ! 7.489270 elapsed 5.609119 user 1.730936 system sec ! [5.618921 user 1.750540 sys total] ! 1/546 [1/546] filesystem blocks in/out ! 0/9287 [0/9496] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/3 [3/6] messages rcvd/sent ! 7/102 [10/105] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand Thanks Tom! -Mitch
[SQL] password encryption
Hello,
I want to encrypt passwords with Postgresql the way mySQL does it with :
UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
How can I do that ?
Thanks a lot,
Laurent
Re: [SQL] Timestamp indexes
"Mitch Vincent" <[EMAIL PROTECTED]> writes: > select * from applicants as a where (a.created::date > '05-01-2000' or > a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 > There is one of the queries.. I just remembered that the order by was added > since last time I checked it's PLAN (in the 6.5.X days) -- could that be the > problem? Probably. With the ORDER BY in there, the LIMIT no longer applies directly to the scan (since a separate sort step is going to be necessary). Now it's looking at a lot more data to be fetched by the scan, not just 10 records, so the indexscan becomes less attractive. Might be interesting to compare the estimated and actual runtimes between this query and what you get with "set enable_seqscan to off;" regards, tom lane
Re: [SQL] Timestamp indexes
With enable_seqscan off (Same query) Sort (cost=9282.89..9282.89 rows=4880 width=611) -> Index Scan using applicants_created, applicants_resubmitted on applicants a (cost=0.00..8983.92 rows=4880 width=611) ...and.. ! system usage stats: ! 7.541906 elapsed 5.368217 user 2.062897 system sec ! [5.391668 user 2.070713 sys total] ! 1/543 [2/543] filesystem blocks in/out ! 0/9372 [0/9585] page faults/reclaims, 0 [0] swaps ! 0 [0] signals rcvd, 0/3 [4/7] messages rcvd/sent ! 7/101 [12/107] voluntary/involuntary context switches ! postgres usage stats: ! Shared blocks: 0 read, 0 written, buffer hit rate = 100.00% ! Local blocks: 0 read, 0 written, buffer hit rate = 0.00% ! Direct blocks: 0 read, 0 written CommitTransactionCommand Looks like that index scan is very unattractive... I'll look for some other ways to speed up the query a bit.. Thanks! -Mitch - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Mitch Vincent" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, July 21, 2000 1:26 PM Subject: Re: [SQL] Timestamp indexes > "Mitch Vincent" <[EMAIL PROTECTED]> writes: > > select * from applicants as a where (a.created::date > '05-01-2000' or > > a.resubmitted::date > '05-01-2000') order by (case when a.resubmitted > > > a.created then a.resubmitted else a.created end) desc limit 10 offset 0 > > > There is one of the queries.. I just remembered that the order by was added > > since last time I checked it's PLAN (in the 6.5.X days) -- could that be the > > problem? > > Probably. With the ORDER BY in there, the LIMIT no longer applies > directly to the scan (since a separate sort step is going to be > necessary). Now it's looking at a lot more data to be fetched by > the scan, not just 10 records, so the indexscan becomes less attractive. > > Might be interesting to compare the estimated and actual runtimes > between this query and what you get with "set enable_seqscan to off;" > > regards, tom lane >
Re: [SQL] Timestamp indexes
"Mitch Vincent" <[EMAIL PROTECTED]> writes: > Looks like that index scan is very unattractive... Yes, though not as bad as the cost estimator thinks (almost a 5:1 ratio in estimated cost, but hardly any difference in real runtime). Still have some work to do in tweaking the estimates, obviously. regards, tom lane
[SQL] Why do I need to set UPDATE permissions for fkey check?
Hello all-
Running: Pg v7.0.2, home rolled, RedHat 6.2 linux.
I am trying to set up a read-only static lookup table, to which other
tables will reference. However, it seems I need to GRANT SELECT, UPDATE
permissions (at least) on the lookup table in order to perform foreign key
integrity checking. This seems strange to me, any ideas as to
why? After filling with data, nothing ever gets updated in this
table! Any suggestions on how I could set up a read-only lookup table
that is involved in foreign key integrity checking?
Thanks! -Jon
Here is the simplest example I could create:
###
-- Create a read-only static lookup table
CREATE TABLE lookup ( id int, value text );
INSERT INTO lookup (id, value) VALUES (1,'hello');
INSERT INTO lookup (id, value) VALUES (2,'world');
REVOKE ALL ON lookup FROM PUBLIC;
GRANT SELECT ON lookup TO PUBLIC;
-- Create the read/write dynamic work table
CREATE TABLE work ( info int references lookup (id) );
REVOKE ALL ON work FROM PUBLIC;
GRANT ALL ON work TO PUBLIC;
###
Now, if I attempt to insert something into the 'work' table:
template1=> \z
Access permissions for database "template1"
Relation | Access permissions
--+
lookup | {"=r"}
work | {"=arwR"}
template1=> INSERT INTO work (info) VALUES (1);
ERROR: lookup: Permission denied.
###
But:
template1=> GRANT UPDATE ON lookup TO PUBLIC;
CHANGE
template1=> \z
Access permissions for database "template1"
Relation | Access permissions
--+
lookup | {"=rw"}
work | {"=arwR"}
template1=> INSERT INTO work (info) VALUES (1);
INSERT 331226 1
--
-**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*---
Jon Lapham
Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas
Universidade Federal do Rio de Janeiro (UFRJ) - Brasil
email: [EMAIL PROTECTED]
***-*--**---***---
Re: [SQL] Why do I need to set UPDATE permissions for fkey check?
It's a known problem in the foreign key code. The reason is that
the fk triggers use SELECT FOR UPDATE to select the matching
rows that it is checking and the reason for using FOR UPDATE is
to lock those rows so that someone cannot delete/change them out
from under your nose while you're looking at them. However,
SELECT FOR UPDATE is asking for update permissions because it
grabs that row lock.
There's still some question of how to get around this. A normal
select is insufficient. Although not a complete solution, setuid triggers
would help (requiring only that the trigger owner had update permissions
not the rest of the users), but I'm not sure when/if this would get done.
- Original Message -
From: "Jon Lapham" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, July 21, 2000 12:36 PM
Subject: [SQL] Why do I need to set UPDATE permissions for fkey check?
> Hello all-
>
> Running: Pg v7.0.2, home rolled, RedHat 6.2 linux.
>
> I am trying to set up a read-only static lookup table, to which other
> tables will reference. However, it seems I need to GRANT SELECT, UPDATE
> permissions (at least) on the lookup table in order to perform foreign key
> integrity checking. This seems strange to me, any ideas as to
> why? After filling with data, nothing ever gets updated in this
> table! Any suggestions on how I could set up a read-only lookup table
> that is involved in foreign key integrity checking?
>
> Thanks! -Jon
>
> Here is the simplest example I could create:
>
> ###
> -- Create a read-only static lookup table
> CREATE TABLE lookup ( id int, value text );
> INSERT INTO lookup (id, value) VALUES (1,'hello');
> INSERT INTO lookup (id, value) VALUES (2,'world');
> REVOKE ALL ON lookup FROM PUBLIC;
> GRANT SELECT ON lookup TO PUBLIC;
>
> -- Create the read/write dynamic work table
> CREATE TABLE work ( info int references lookup (id) );
> REVOKE ALL ON work FROM PUBLIC;
> GRANT ALL ON work TO PUBLIC;
> ###
>
> Now, if I attempt to insert something into the 'work' table:
>
> template1=> \z
> Access permissions for database "template1"
> Relation | Access permissions
> --+
> lookup | {"=r"}
> work | {"=arwR"}
>
> template1=> INSERT INTO work (info) VALUES (1);
> ERROR: lookup: Permission denied.
>
> ###
>
> But:
> template1=> GRANT UPDATE ON lookup TO PUBLIC;
> CHANGE
> template1=> \z
> Access permissions for database "template1"
> Relation | Access permissions
> --+
> lookup | {"=rw"}
> work | {"=arwR"}
>
> template1=> INSERT INTO work (info) VALUES (1);
> INSERT 331226 1
Re: [SQL] Why do I need to set UPDATE permissions for fkey check?
On Fri, Jul 21, 2000 at 02:00:00PM -0700, Stephan Szabo wrote: > > It's a known problem in the foreign key code. The reason is that > the fk triggers use SELECT FOR UPDATE to select the matching > rows that it is checking and the reason for using FOR UPDATE is > to lock those rows so that someone cannot delete/change them out > from under your nose while you're looking at them. However, > SELECT FOR UPDATE is asking for update permissions because it > grabs that row lock. Oh, okay, I understand your explanation, and it fits with what I am seeing. But... ...this is a READ ONLY table! Maybe it would be possible to have the fkey triggers look to see if the table is read-only, and then simply use SELECT instead of SELECT FOR UPDATE and then not perform the row locking? Since this is a read-only table, there would be no risk of deleting/changing any of the data. Yeah, I realize that with this solution, you cannot guarantee that the table doesn't become 'writable' sometime during the fkey lookup. It would seem to me that this is a serious problem. I absolutely cannot have my data table be writable, and I need to maintain fkey integrity. Urg this is very bad, the fkey integrity check is the reason I installed Pg v7. I would think that keeping read-only static data table would be a common database occurance, any suggestions on how to get around this issue? Possibly with a (gulp) permissions switching trigger (gulp)? -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham Centro Nacional de Ressonancia Magnetica Nuclear de Macromoleculas Universidade Federal do Rio de Janeiro (UFRJ) - Brasil email: [EMAIL PROTECTED] ***-*--**---***---
Re: [SQL] problem with view and case - please help
the idea of a funtion is a good idea, thanks a lot ! i am a newbie to sql, after some try, i have made a function like this : create function browser(text) returns text AS 'SELECT CASE WHEN $1 LIKE \'%MSIE 2.0;%\' THEN \'Internet Explorer 2.0\' WHEN $1 LIKE \'%MSIE 3.0;%\' THEN \'Internet Explorer 3.0\' WHEN $1 LIKE \'%MSIE 3.0a;%\' THEN \'Internet Explorer 3.0a\' WHEN $1 LIKE \'%MSIE 3.0B;%\' THEN \'Internet Explorer 3.0B\' WHEN $1 LIKE \'%MSIE 3.01;%\' THEN \'Internet Explorer 3.01\' WHEN $1 LIKE \'%MSIE 3.02;%\' THEN \'Internet Explorer 3.02\' WHEN $1 LIKE \'%MSIE 4.0b1;%\' THEN \'Internet Explorer 4.0 beta 1\' WHEN $1 LIKE \'%MSIE 4.0;%\' THEN \'Internet Explorer 4.0\' WHEN $1 LIKE \'%MSIE 4.01;%\' THEN \'Internet Explorer 4.01\' WHEN $1 LIKE \'%MSIE 4.5;%\' THEN \'Internet Explorer 4.5\' WHEN $1 LIKE \'%MSIE 5.0b1;%\' THEN \'Internet Explorer 5.0 beta 1\' WHEN $1 LIKE \'%MSIE 5.0b2;%\' THEN \'Internet Explorer 5.0 beta 2\' WHEN $1 LIKE \'%MSIE 5.0;%\' THEN \'Internet Explorer 5.0\' WHEN $1 LIKE \'%MSIE 5.01;%\' THEN \'Internet Explorer 5.01\' WHEN $1 LIKE \'%MSIE 5.5b1;%\' THEN \'Internet Explorer 5.5 beta 1\' WHEN $1 LIKE \'%MSIE 5.5;%\' THEN \'Internet Explorer 5.5\' WHEN $1 = \'Mozilla\' THEN \'Netscape version inconnue\' WHEN $1 LIKE \'Mozilla (X11; I; Linux 2.0.32 i586)%\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape Linux version non connue\' WHEN $1 LIKE \'Mozilla/2.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape 2.0\' WHEN $1 LIKE \'Mozilla/2.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape 2.02\' WHEN $1 LIKE \'Mozilla/2.02E %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape 2.02E\' WHEN $1 LIKE \'Mozilla/3.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape 3.0\' WHEN $1 LIKE \'Mozilla/3.01 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape 3.01\' WHEN $1 LIKE \'Mozilla/3.02 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/3.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.03 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.04 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.05 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.06 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.07 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.08 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.5 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.51 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.6 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.61 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.7 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.71 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.72 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/4.73 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/5.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' WHEN $1 LIKE \'Mozilla/6.0 %\' AND $1 NOT LIKE \'%MSIE%\' AND $1 NOT LIKE \'%compatible%\' THEN \'Netscape\' ELSE $1 END' language 'SQL'; and now i can get this query to work : select browser(agent_i) as navigateur,count( browser(agent_i)) as total from access group by navigateur order by total asc; wich is the result i search ! thanks a lot to everyone Ange Volker Paul a écrit : > > > CREATE VIEW browser > > AS > > SELECT > > agent_i, > > CASE > > WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0' > > ... > > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > > WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND > > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > > WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND > > agent_i NOT LIKE '%compatible%' THEN 'Netscape' > > ELSE agent_i END AS navigateur, count (agent_i) > > as total from access group by agent_i; > > pqReadData() -- backend closed the channel unexpectedly. > > This probably means the backend terminate
[SQL] problem with view and case - please help
I repost my message because it seems that my previous post don't go on i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2] rpm version of Linux Mandrake 7.02 i try this query : [ange@ange ange]$ psql zonecommerce -h 192.0.1.84 -u -f toto.sql Username: postgres Password: DROP VIEW liste_browser ; ERROR: Rule or view 'liste_browser' not found < this ok because view does not exist at this time CREATE VIEW browser AS SELECT agent_i, CASE WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0' WHEN agent_i LIKE '%MSIE 3.0;%' THEN 'Internet Explorer 3.0' WHEN agent_i LIKE '%MSIE 3.0a;%' THEN 'Internet Explorer 3.0a' WHEN agent_i LIKE '%MSIE 3.0B;%' THEN 'Internet Explorer 3.0B' WHEN agent_i LIKE '%MSIE 3.01;%' THEN 'Internet Explorer 3.01' WHEN agent_i LIKE '%MSIE 3.02;%' THEN 'Internet Explorer 3.02' WHEN agent_i LIKE '%MSIE 4.0b1;%' THEN 'Internet Explorer 4.0 beta 1' WHEN agent_i LIKE '%MSIE 4.0;%' THEN 'Internet Explorer 4.0' WHEN agent_i LIKE '%MSIE 4.01;%' THEN 'Internet Explorer 4.01' WHEN agent_i LIKE '%MSIE 4.5;%' THEN 'Internet Explorer 4.5' WHEN agent_i LIKE '%MSIE 5.0b1;%' THEN 'Internet Explorer 5.0 beta 1' WHEN agent_i LIKE '%MSIE 5.0b2;%' THEN 'Internet Explorer 5.0 beta 2' WHEN agent_i LIKE '%MSIE 5.0;%' THEN 'Internet Explorer 5.0' WHEN agent_i LIKE '%MSIE 5.01;%' THEN 'Internet Explorer 5.01' WHEN agent_i LIKE '%MSIE 5.5b1;%' THEN 'Internet Explorer 5.5 beta 1' WHEN agent_i LIKE '%MSIE 5.5;%' THEN 'Internet Explorer 5.5' WHEN agent_i = 'Mozilla' THEN 'Netscape version inconnue' WHEN agent_i LIKE 'Mozilla (X11; I; Linux 2.0.32 i586)%' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape Linux version non connue' WHEN agent_i LIKE 'Mozilla/2.0 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.0' WHEN agent_i LIKE 'Mozilla/2.02 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.02' WHEN agent_i LIKE 'Mozilla/2.02E %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape 2.02E' WHEN agent_i LIKE 'Mozilla/3.0 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape 3.0' WHEN agent_i LIKE 'Mozilla/3.01 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape 3.01' WHEN agent_i LIKE 'Mozilla/3.02 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/3.03 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.0 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.03 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.04 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.05 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.06 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.07 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.08 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.5 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.51 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.6 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.61 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.7 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.71 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.72 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/4.73 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/5.0 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' WHEN agent_i LIKE 'Mozilla/6.0 %' AND agent_i NOT LIKE '%MSIE%' AND agent_i NOT LIKE '%compatible%' THEN 'Netscape' ELSE agent_i END AS navigateur, count (agent_i) as total from access group by agent_i; pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. We have lost the connection to the backend, so further processing is impossible. Terminating. [ange@ange ange]$ what is the problem with my query ? is it a bug ? another problem is that after backe
[SQL] create function - user permissions
Hi, one of my users wants to create functions using the C language, but pgs says "no permission". How can I permit the user to do this, while avoiding to give him root access rights? Thanks, Markus
Re: [SQL] create function - user permissions
Markus Wagner <[EMAIL PROTECTED]> writes: > one of my users wants to create functions using the C language, but pgs > says "no permission". > How can I permit the user to do this, while avoiding to give him root > access rights? Just a wakeup call here: if you let a user write C functions then you *are* giving him Postgres superuser rights. He can do anything he damn well pleases as user postgres, he's just got to write some code to do it. Once you've absorbed that, giving him the dbadmin password should not look unreasonable. regards, tom lane
Re: [SQL] password encryption
On Fri, 21 Jul 2000, Silesky Marketing Inc, Support wrote:
> Hello,
>
> I want to encrypt passwords with Postgresql the way mySQL does it with :
>
> UPDATE user SET Password=PASSWORD('new_password') WHERE user='root';
Check out the ALTER command.
ALTER USER username
[ WITH PASSWORD 'password' ]
[ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
[ VALID UNTIL 'abstime' ]
Rod
--
Roderick A. Anderson
[EMAIL PROTECTED] Altoplanos Information Systems, Inc.
Voice: 208.765.6149212 S. 11th Street, Suite 5
FAX: 208.664.5299 Coeur d'Alene, ID 83814
[SQL] Re: [BUGS] problem with view and case - please help
Ange Michel POZZO wrote: > I repost my message because it seems that my previous post don't go on > > i use [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2] > rpm version of Linux Mandrake 7.02 > That's definitely the problem. I cannot recreate it with current CVS sources. The view generates a rewrite rule of ~40K. So v6.5.2 wouldn't be able to store it anyway. 7.0 should, because it seems extremely good compressable (octet length of 2.7K). So give our latest release a try. > i try this query : > > [ange@ange ange]$ psql zonecommerce -h 192.0.1.84 -u -f toto.sql > Username: postgres > Password: > > DROP VIEW liste_browser ; > ERROR: Rule or view 'liste_browser' not found < this ok > because view does not exist at this time > CREATE VIEW browser > AS > SELECT > agent_i, > CASE > WHEN agent_i LIKE '%MSIE 2.0;%' THEN 'Internet Explorer 2.0' > WHEN agent_i LIKE '%MSIE 3.0;%' THEN 'Internet Explorer 3.0' > WHEN agent_i LIKE '%MSIE 3.0a;%' THEN 'Internet Explorer 3.0a' > WHEN agent_i LIKE '%MSIE 3.0B;%' THEN 'Internet Explorer 3.0B' > WHEN agent_i LIKE '%MSIE 3.01;%' THEN 'Internet Explorer 3.01' > WHEN agent_i LIKE '%MSIE 3.02;%' THEN 'Internet Explorer 3.02' > WHEN agent_i LIKE '%MSIE 4.0b1;%' THEN 'Internet Explorer 4.0 beta 1' > [...] OTOH, the previous suggestion of using a function seems more appropriate. PL/Tcl for example has very powerful regular expression capabilities, that could simplify it alot. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[SQL] Re: [BUGS] problem with view and case - please help
Ange Michel POZZO <[EMAIL PROTECTED]> writes: > CREATE VIEW browser > AS > SELECT > agent_i, > CASE > < massive CASE expression > > ELSE agent_i END AS navigateur, count (agent_i) > as total from access group by agent_i; > pqReadData() -- backend closed the channel unexpectedly. Not sure why you are seeing a crash instead of a complaint, but there's no chance of making such a huge VIEW expression work in 6.5 --- the rule text won't fit in 8K. Try it in 7.0.2 (which has still got the 8K limit, but at least it compresses the text). BTW I tend to agree with the other comment that this seems a clumsy way to go about it. I'd think about making a table containing a pattern column and a browser-name column and doing the view as a join. Might be a little tricky to ensure you get only one match, however ... regards, tom lane
