[SQL] Indexable (constant LIKE prefix_keyfield) ?
I was impressed that LIKE operator can be indexed in 7.4 with non-C
locale. But how about the reverse condition?
What I need is to:
SELECT * FROM prefixes WHERE 'literal' LIKE prefix || '%';
or
SELECT * FROM prefixes WHERE 'literal' ~ ('^' || prefix);
Prefix is of type text (variable-length), which may contain only ASCII
chars (database runs under ru_RU.KOI8-R locale). Only the longest prefix
should be taken if more than one matches.
There's no strict definition for "prefixes" yet, and I seek for how to
make it possible to use an index by this query. The ways I see:
1. Sequentially rtrim('literal') and compare it to prefix.
Really bad idea.
2. Use 2 fields: prefix_le and prefix_gt, then
'literal' >= prefix_le AND 'literal' < prefix_gt
(or 'literal' ~>=~ prefix_le AND 'literal' ~<~ prefix_gt, but it
seems there's no need to).
a) supply both fields from outside (I don't like this idea).
b) supply only prefix (=prefix_le), and calculate prefix_gt (using
trigger?) as prefix_le "plus one".
Digging the backend sources, I've found make_greater_string used
to expand indexable LIKE or regexp condition. Can I use it for my
needs somehow? Or have I to write my own in this case?
3. Create some magical index I dunno about :)
4. SELECT * FROM prefixes
WHERE prefix <= 'literal' AND 'literal' LIKE prefix || '%'
ORDER BY prefix DESC LIMIT 1;
Looks like the best way, but I'm not sure this is always correct.
Comments, suggestions, please?
--
Fduch M. Pravking
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] calling function , passing OLD as argument
Hello. I am a beginner with pgsql and struggeling with the documentation. With the help from the folks on the PSQL Novice List I managed to work out that declaring CREATE OR REPLACE FUNCTION deny_namen_telefonverweise(namen_telefonverweise) RETURNS void AS ' DECLARE ... would work where the function at the bottom fails with test=# \i '/home/myAccount/Documents/Datenbank Layout/Postgres Sytax/test/Regeln2' CREATE FUNCTION psql:/home/myAccount/Documents/Datenbank Layout/Postgres Sytax/test/Regeln2:16: ERROR: function deny_namen_telefonverweise(namen_telefonverweise) does not exist HINT: No function matches the given name and argument types. You may need to add explicit type casts. test=# But I have lots of these functions since I have lots of tables, so what I really want is one function reusable for all parameters which are tables. Question: Would it be possible to pass the table name to the function as variable and EXECUTE then the action I want the function to do? How would I convert OLD to the table name old refers to? Is there another way of letting the function know on which row the rule was called? CREATE OR REPLACE FUNCTION deny_namen_telefonverweise(record) RETURNS void AS ' DECLARE result RECORD; BEGIN /* DELETE ROW LATER */ SELECT 1+1; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE RULE deny_namen_Telefonverweise AS ON DELETE TO Namen_Telefonverweise DO INSTEAD SELECT DENY_Namen_Telefonverweise(OLD); ---(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
Re: [SQL] Secure DB Systems - How to
In the last exciting episode, [EMAIL PROTECTED] (Rajesh Kumar Mallah) wrote:
> Sarah Tanembaum wrote:
>
>>I was wondering if it is possible to create a secure database system
>>usingPostgreSQL/PHP combination?
>>
>>I have the following in mind:
>>
>>I wanted to store all my( and my brothers and sisters) important document
>>information such as birth certificate, SSN, passport number, travel
>>documents, insurance(car, home, etc) document, and other important documents
>>imagined in the database.
>>
>>The data will be entered either manually and/or scanned(with OCR). I need to
>>be able to search on all the fields in the database.
>>
>>We have 10 computers(5bros, 4sisters, and myself) plus 1 server with I
>>maintained. The data should be synchronize/replicate between those
>>computers.
>>
>>Well, so far it is easy, isn't it?
>>
>>Here's my question:
>>
>>a) How can I make sure that it secure so only authorized person can
>>modify/add/delete the information? Beside transaction logs, are there any
>>other method to trace any transaction(kind of paper trail)?
>>
>>
> There can be multiple solutions to your problem.
>
> The security and logging may be implemented either at
> database level or application level. That is a call you have to
> take.
Doing it at the database level means having to trust anyone that has
administrative access to the database system.
The only way for this to NOT require trusting the administrators is to
store data in some sort of encrypted form, where the data is NOT
visible except when someone decrypts it within the client application.
The main work published on the subject is _Translucent Databases_, by
Peter Wayner. Here are a bunch of links that give a pretty good idea
of what it's about.
http://www.oreillynet.com/pub/a/network/2002/08/02/simson.html
http://www.wayner.org/books/td/
http://www.wayner.org/books/td/faq.php
http://www.linux-mag.com/2003-12/databases_01.html
They discuss it from the perspective of using Java as the "client
application" layer; presumably PHP offers some cryptographic tools to
allow doing similar things...
http://hotwired.lycos.com/webmonkey/programming/php/tutorials/tutorial1.html
--
output = ("cbbrowne" "@" "ntlug.org")
http://cbbrowne.com/info/languages.html
"To do is to be." -- Aristotle
"To be is to do." -- Socrates
"Do be do be do." -- Sinatra
"Do be a do bee." -- Miss Sally of Romper Room fame.
"Yabba dabba do." -- Fred Flintstone
"DO...BEGIN..END" -- Niklaus Wirth
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
