Re: [SQL] [GENERAL] arrays
I was wondering why is such a rage against arrays. I posted 2 very common problems where arrays provide the only natural (and efficient) fit. (and got no responses) So it seems to me that: - Arrays implementation (along with the intarray package) in postgresql is well performing and stable. - Some problems shout out for array usage. - The Array interface is defined in java.sql package. (I dont know if sql arrays is in some standard but it seems that Java sees it that way, at least). - The Array interface is implemented in the official postgresql java package. - In some problems replacing arrays according the tradition relational paradigm would end up in a such a performance degradation, that some applications would be unusable. - Oleg and Teodor did a great job in intarray, making array usage easy and efficient. Thanx! == Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel:+30-10-8981112 fax:+30-10-8981877 email: [EMAIL PROTECTED] [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] alter user does not changes password
Hi all: I know it's a silly question, but I've googling around and searching mailist archive with no answer to my question: I'm using self compiled PostgreSQL 7.2.1 in a RH 7.1 box. As described in http://www.postgresql.org/idocs/index.php?sql-alteruser.html I'm trying to change a user's password as: alter user camara with password 'canabis!'; but when I try to connect: $ psql -U camara dbcamara; User "camara" logs with no password If I use -W option of psql It prompts for password and I can enter whatever I want... it accepts! It must be a feature.. not a bug... I know I'm making a mistake.. but.. where?? bests regards -- []'s Lucas Brasilino [EMAIL PROTECTED] http://www.recife.pe.gov.br Emprel -Empresa Municipal de Informatica (pt_BR) Municipal Computing Enterprise (en_US) Recife - Pernambuco - Brasil Fone: +55-81-34167078 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Stored Procedures
On Tue, Oct 01, 2002 at 06:16:57PM +, [EMAIL PROTECTED] wrote: > Hi all. I'm looking for a little help here. I have a > project where I have to write some stored proceedures > and am having some problems. My main issue is, I cannot > figure out how to return a record set containing > multipule columns. I am looking for a few examples on > how I can do this. Most of what I have to do is fairly > simple SQL queries based on a pramater sent to the > function. I tried to use the SETOF option, > but only get back one column. In 7.2 this is acomplished through returning a cursor from the function. See the 7.3 documentation to see how to do that (AFAIK, this is not documented in the 7.2 docs, although it does work). In 7.3 you can return true record sets without the use of cursors. Again, see the docs for 7.3 in the developers site. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Q: How many IBM CPU's does it take to do a logical right shift? A: 33. 1 to hold the bits and 32 to push the register. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] please help with converting a view in oracle into postgresql readably code
Hello, I am trying to convert an application to postgresql, and am having a bear of a time converting the following view (from oracle). What it does, just in case you aren't very familiar with oracle syntax, is group the average reg_state from the events_registrations table after having converted the varchar variables present in reg_state, into numbers to represent the various possibilities). Then, once it has found the average number for all items in the table events_registrations with the same order_id number it fills in a human readable word. Thanks for any help in letting me either know how to create this view, or to tell me how to convert the variables in the varchar field reg_state into the numbers I want them to represent which I will be able to manipulate with things such as floor() and avg(). Matthew Geddert = create or replace view events_orders_states as select o.*, o_states.order_state from events_orders o, (select order_id, decode (floor(avg (decode (reg_state, 'canceled', 0, 'waiting', 1, 'pending', 2, 'shipped', 3, 0))), 0, 'canceled', 1, 'incomplete', 2, 'incomplete', 3, 'fulfilled', 'void') as order_state from events_registrations group by order_id) o_states where o_states.order_id = o.order_id; ---(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] alter user does not changes password
> alter user camara with password 'canabis!'; > > but when I try to connect: > > $ psql -U camara dbcamara; > > User "camara" logs with no password If I use -W option of psql > It prompts for password and I can enter whatever I want... it accepts! > > It must be a feature.. not a bug... I know I'm making a mistake.. > but.. where?? can you quote pg_hba.conf (without comments naturally) here? ---(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] alter user does not changes password
Lucas Brasilino <[EMAIL PROTECTED]> writes: > but when I try to connect: > $ psql -U camara dbcamara; > User "camara" logs with no password Did you adjust pg_hba.conf to require password authentication? If so, did you SIGHUP the postmaster after changing the file? regards, tom lane ---(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]
Re: [SQL] [GENERAL] arrays
Achilleus, > I was wondering why is such a rage against arrays. > > I posted 2 very common problems where arrays provide > the only natural (and efficient) fit. (and got no responses) > So it seems to me that: All of your points are correct. Us "old database hands" have a knee-jerk reaction against arrays for long-term data storage because, much of the time, developers use arrays because they are lazy or don't understand the relational model instead of because they are the best thing to use. This is particularly true of people who come to database development from, say, web design. In this thread particularly, Mike was suggesting using arrays for a field used in JOINs, which would be a royal mess. Which was why you heard so many arguments against using arrays. Or, to put it another way: 1. Array data types are perfect for storing data that arrives in the form of arrays or matricies, such as scientific data , or interface programs that store arrays of object properties. 2. For other purposes, arrays are a very poor substitute for proper sub-table storage of related data according to the relational model. 3. The distinguishing factor is "atomicity": ask yourself: "is this array a discrete and undivisible unit, or is is a collection of related but mutable elements?" If the former, use and array. If the latter, use a sub-table. Clearer now? -Josh Berkus ---(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] Stored Procedures
Hi all. I'm looking for a little help here. I have a project where I have to write some stored proceedures and am having some problems. My main issue is, I cannot figure out how to return a record set containing multipule columns. I am looking for a few examples on how I can do this. Most of what I have to do is fairly simple SQL queries based on a pramater sent to the function. I tried to use the SETOF option, but only get back one column. Any help will be would be greatly appricated. Simple examples would be of a great help. Thanks, Ben ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] please help with converting a view in oracle into postgresql readably code
On Tue, Oct 01, 2002 at 10:41:17PM -0700, mgeddert wrote: > create or replace view events_orders_states as select o.*, o_states.order_state from events_orders o, ( SELECT order_id, CASE ( floor (avg ( CASE reg_state WHEN 'canceled' THEN 0 WHEN 'waiting' THEN 1 WHEN 'pending' THEN 2 WHEN 'shipped' THEN 3 ELSE 0)) ) WHEN 0 THEN 'canceled' WHEN 1 THEN 'incomplete' WHEN 2 THEN 'incomplete' WHEN 3 THEN 'fulfilled' ELSE 'void') as order_state FROM events_registrations GROUP BY order_id ) o_states WHERE o_states.order_id = o.order_id; Oracle 9 does support SQL92-compliant CASE WHEN. It's much more clear than using cryptic decode to me. See http://www.postgresql.org/idocs/index.php?functions-conditional.html Hope this works. -Roberto P.S.: Some indenting goes a long way through helping to understand your code. -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + "Hello, World!" 17 Errors, 31 Warnings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
