[GENERAL] pgadmin3 on Gentoo
Hi, I'm really happy to see that pgadmin3 will run on Linux! I tried to build it on Gentoo but got errors. Was anybody of you able to build it for Gentoo or will there be an emerge file? Regards, Christian ---(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: [GENERAL] pl/tcl trigger question
Op 26 Aug 2003 (12:38), schreef Robert Treat <[EMAIL PROTECTED]>: > On Tue, 2003-08-26 at 07:28, Jules Alberts wrote: > > Hello everyone, > > > > I'm working on a tiny trigger function that needs to ensure that all > > values entered in a field are lowercase'd. I can't use pl/pgsql > > because I have a dozen different columns (with different names) that > > need a trigger that does this and pl'pgsql can't expand variable names > > to fieldnames. Writing a dozen functions (one per columnname) is /way/ > > too blunt so I tried pl/tcl (which I don't know): > You'll need a function a bit more complex than this, but to do what your > trying to do in the function above the function would be written as: > > create or replace function my_lowercase() returns trigger as ' > set NEW($1) [string tolower $NEW($1)] > return [array get NEW]' language 'pltcl'; > > Hope this helps, please post the final results when you get there. Hi Robert, It works great, thanks a lot! There is one little issue though: when I insert null values, the function fails. I think I can work around this by giving the columns a default value of '' in my table design, but I would like a more defensive approach, I.E. having my_lowercase() check for null values. Thanks again for any help, and sorry if I'm asking basic TCL questions, I don't know the language. Do you happen to know a good site where the language is explained? All I googled was about creating widgets, GUI stuff :-( > Robert Treat ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] delivering database stand-alone
hi all, i am planning to build a database (a dictionary in fact) that i will eventually want to distribute on a cd (or downloadable iso). what i would like to know is if this is technically possible with postgresql. and how exactly would it be done? would i have to make postgresql run off the cd, or should it first be installed to the hard disk? (the latter would be problematic on linux, given the many different distros and their different package management systems...) how would i deal with systems that already have a postgreql server or (more difficult perhaps) a different database server running? or should i not make use of postgresql (or any database server) at all for the cd? after all, the data in the database is static, users will not have to modify it, just look it up. thanks for any comments and suggestions. -- Joost Kremers Life has its moments ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] About GPL and proprietary software
This is neither a GNU nor a MySQL mailing list. I suggest you take your question to one of those places, as you'll get a better answer. The question in it self was more general than PostgreSQL and MySQL. However as I currently need to work with both of them I wanted to make clear the actual differences caused by licensing between the two products. The other thing is that it is probably a little easier to explain and understand if there are concrete examples. I asked this list because I didn't want to subscribe to yet another list for this matter and pgsql-general sure has the competence to answere that kind of questions. It has to have. I asked this list and not MySQL list because I am subscribed to mysql-general and comparing the two list content I like more what I see in PostgreSQL lists and the concrete example is about PostgreSQL as much as it is MySQL. I don't think at a PostgreSQ conference you would guide me to MySQL conference when I asked this same question would you? Licensing is quite difficult to understad (at least for me) yet a very important aspect of software development. I don't intend to ruine anybodys day I just want a small discussion which in the end would explain the cases when the licensing aspect of these two concrete products will play an important role in developing software. Kaarel ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] About GPL and proprietary software
On Sun, Aug 31, 2003 at 04:14:30PM -0400, Christopher Browne wrote: > > Sure? My understanding is that it does break GPL. That's why there's an > > LGPL. > > > > But since MySQL is double licensed and GPl is just one of the two it > > gets even more complicated. > > No, actually, it's pretty simple. >... > The issue is that the CLIENT software is unavailable in other than GPL > and other "private license-for-money" forms. > > In order for you to use MySQL, you have to link GPLed libraries in > with your code. There are no LGPLed libraries; that would break MySQL > AB's business model. Well, isn't that what I said? Okay, granted, you put it into words much better, but it looks like I wanted to say the same. MySQL has to GPL the libs to make sure they can make money with there other license. What I meant to say with LGPL is that this license is for such a usage, not that MySQl has LGPLed stuff. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] mysql's last_insert_id
On Fri, 2003-08-29 at 04:11, Martijn van Oosterhout wrote: > OIDs have never beebn unique, it's just that most databases never get big > enough to experience wraparound. They are also now optional per table and > may soon no longer be available by default. It would be a god idea to remove the oid reference from the FAQ 4.15.2, to make sure no one in the futhere makes my mistake again :-) /BL ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] SELECT Question
Jeffrey,
second solution is a beauty... thanks a lot.
Alex
Jeffrey Melloy wrote:
If I'm understanding you correctly, you can do something like:
select cola,
colb,
exists
(select 'x'
from tableb
where colc = colb)
from tablea
Since that has a subselect, you may get better performance with
something like this:
select cola,
colb,
case when colc is null
then 'f' else 't' end as exists
from table1 left join table2 on colb = colc;
jmelloy=# create table table1(cola serial, colb char);
NOTICE: CREATE TABLE will create implicit sequence 'table1_cola_seq'
for SERIAL column 'table1.cola'
CREATE TABLE
jmelloy=# create table table2 (colc char);
CREATE TABLE
jmelloy=# insert into table1 (colb) values ('A');
INSERT 1551538 1
jmelloy=# insert into table1 (colb) values ('B');
INSERT 1551539 1
jmelloy=# insert into table1 (colb) values ('a');
INSERT 1551540 1
jmelloy=# insert into table2 values ('B');
INSERT 1551541 1
jmelloy=# select cola, colb, exists (select 'x' from table2 where colc
= colb) from table1;
cola | colb | ?column?
--+--+--
1 | A| f
2 | B| t
3 | a| f
(3 rows)
jmelloy=# select cola, colb, case when colc is null then 'f' else 't'
end as exists from table1 left join table2 on colb = colc;
cola | colb | exists
--+--+
1 | A| f
2 | B| t
3 | a| f
(3 rows)
On Sunday, August 31, 2003, at 12:03 PM, Alex wrote:
Hi,
I need to form a query where i can add some columns based on the result.
Table A
ColA, ColB
--
1 A
2 B
3 A
Table B
ColC
A
If A exists if would like the result back as
1 A OK
2 B NG
3 A OK
Is it possible to replace the value in the query ?
Thanks
Alex
---(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 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 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Getting last inserted SERIAL
Read the FAQ. Your currval assumption is wrong.
---
mgarriss wrote:
> Given this table:
>
> CREATE TABLE test ( id SERIAL, example TEXT );
>
> An implicit sequence is created as show in this message:
>
> NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for
> SERIAL column 'test.id'
>
> How do I retrieve the last 'id' that was inserted? I have a process
> that does an insert and then needs the value of the id column of the row
> it just inserted so that that row can be used later in processing.
> First thought is "SELECT CURRVAL('test_id_seq');" but this assumes that
> there is only one connection inserting into this table, bad assumption.
> Next idea is the make 'id' an INT4 and set it explicitly with a value
> that I select from an explicitly created sequence. This method seems a
> bit inelegant. Any ideas?
>
> TIA
> Michael Garriss
>
>
> ---(end of broadcast)---
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match
>
--
Bruce Momjian| http://candle.pha.pa.us
[EMAIL PROTECTED] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup.| Newtown Square, Pennsylvania 19073
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Getting last inserted SERIAL
On Sun, 31 Aug 2003, mgarriss wrote:
> First thought is "SELECT CURRVAL('test_id_seq');" but this assumes that
> there is only one connection inserting into this table, bad assumption.
That is what you should use, and it works for concurrent sessions. It's
all described in the manual:
http://www.postgresql.org/docs/7.3/static/functions-sequence.html
--
/Dennis
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] About GPL and proprietary software
Martijn van Oosterhout wrote: > > Right, dynamic linking is a case where RMS would like the GPL to spread > > the the closed-source binary, but I don't think he can legally do that. > > > > We do have that issue with our linking in of libreadline. We may adopt > > libedit someday for that very reason. > > I was under the impression that the GPL only covers distribution, not use > (as seems normal for copyright). In other words, as long as you don't ship > readline with PostgreSQL you're fine. If the user wants to install it on > their machine with readline linked in that's their problem entirely. > > Now, I think that people have tried to argue that if a library is the *only* > implementation of the interface then it should be considered linked in > because otherwise you're just using dynamic linking to get around the GPL. > > But since PostgreSQL doesn't depend on readline (it is optional after all) I > don't see the issue. However, for the MySQL client library since the > software strictly depends on that library, the fact that it's distributed as > a separate tarball does not absolve you of the GPL requirement. > > Obviously MySQL wouldn't have done their license this way if they didn't > think it was enforceable. Maybe they have themselves an exception or > variation on the GPL? But it's still confusing. The FSF would _like_ dynamic linking to pass the GPL to the closed-source binary, but that doesn't make it so --- I would like a lot of things but wanting it to happen isn't enough. Their FAQ says (http://www.gnu.org/licenses/gpl-faq.html): What is the difference between "mere aggregation" and "combining two modules into one program"? Mere aggregation of two programs means putting them side by side on the same CD-ROM or hard disk. We use this term in the case where they are separate programs, not parts of a single program. In this case, if one of the programs is covered by the GPL, it has no effect on the other program. Combining two modules means connecting them together so that they form a single larger program. If either part is covered by the GPL, the whole combination must also be released under the GPL--if you can't, or won't, do that, you may not combine them. What constitutes combining two parts into one program? This is a legal question, which ultimately judges will decide. We believe that a ^^^ proper criterion depends both on the mechanism of communication (exec, pipes, rpc, function calls within a shared address space, etc.) and the semantics of the communication (what kinds of information are interchanged). You can bet that RMS, control freak that he is, wouldn't have put that disclaimer in there if he felt he had much chance of making the GPL dynamic linking restriction enforceable. A more exotic issue is: what if you create a libreadline library that has the same linking signature as GNU readline, but it does nothing. Can you then say the binary doesn't _require_ GNU readline? As you can see, saying something _requires_ something else to run is a very hard argument to make, and even if the argument can be made, saying that the function calls themselves force the GPL is a great reach, I think. It isn't even clear that the GPL is enforceable in saying you can't modify the source code and ship a closed source version. But if it is, reaching from there to say you can't dynamically call a GPL library is really strange. How is that different from calling the Linux kernel, which is GPL? In fact, most system calls are accessed through a libc function call. Of course, GNU libc is LGPL, but it makes calls to a GPL kernel. Does the LGPL kernel remove the GPL dynamic linking restriction to the kernel? I don't think so. In fact, I don't think a line can clearly be drawn, and hence the unenforceable of a the dynamic linking GPL restriction. On a (very) side note, there are some things that you grow to like more and more over time (hopefully PostgreSQL), while there are others you grow to like less and less over time (GPL, RMS). The RMS case is particularly poignant because new folks to open source really seem to like him, but the longer they are involved in open source, the less they seem to like him. I have seen this regularly in my travels. In fact, when someone wrote a biography of RMS, the author was most surprised that he could find so few of his acquaintances who would talk about him. Another big part of the success of PostgreSQL is that people seem to like us more and more over time, while proprietary vendors seem to generate the opposite effect. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road
Re: [GENERAL] how to group by a joined query?
Weiping He wrote: suppose I've got two table: laser_uni=# \d t1 Table "public.t1" Column | Type | Modifiers +--+--- name | text | addr | text | laser_uni=# \d t2 Table "public.t2" Column | Type | Modifiers +-+--- name | text| len| integer | of | integer | and I want to use join to select out data and then group by one column, like this: laser_uni=# select t1.name, t1.addr, t2.name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t2.name; ERROR: Attribute t1.name must be GROUPed or used in an aggregate function seems the I must gorup all those fields: laser_uni=# select t1.name as t1name, t1.addr as t1addr, t2.name as t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t1.name, t1.addr, t2.name, t2.len, t2.of; t1name | t1addr | t2name | len | of +++-+ || henry | 2 | 4 || laser | 4 | 4 (2 rows) is it specification compliant or postgresql specific? Thanks reread the docs, seems use DISTINCE ON clause solved my problem: select distinct on( t2.len) t1.name as t1name, t1.addr as t1addr, t2.name as t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name; Thanks Laser ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[GENERAL] Getting last inserted SERIAL
Given this table:
CREATE TABLE test ( id SERIAL, example TEXT );
An implicit sequence is created as show in this message:
NOTICE: CREATE TABLE will create implicit sequence 'test_id_seq' for
SERIAL column 'test.id'
How do I retrieve the last 'id' that was inserted? I have a process
that does an insert and then needs the value of the id column of the row
it just inserted so that that row can be used later in processing.
First thought is "SELECT CURRVAL('test_id_seq');" but this assumes that
there is only one connection inserting into this table, bad assumption.
Next idea is the make 'id' an INT4 and set it explicitly with a value
that I select from an explicitly created sequence. This method seems a
bit inelegant. Any ideas?
TIA
Michael Garriss
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [GENERAL] Prevent from Deletion
Alex <[EMAIL PROTECTED]> writes: > Hi, > I have tables that have default records that must not be deleted or > modified. > Is there an easy way to do this. Like setting a trigger on the Primary > key value ? You could do this--create ON UPDATE and ON DELETE triggers that look for distinguishing features of the default records (primary key value or whatever) and RAISE ERROR if they match. They'll be executed for every UPDATE and DELETE on the table, which may or may not be a performance issue for you... -Doug ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] About GPL and proprietary software
On Sun, Aug 31, 2003 at 07:37:47PM -0400, Bruce Momjian wrote: > Ron Johnson wrote: > > Well, there's this: > > http://www.fsf.org/licenses/gpl-faq.html#LinkingWithGPL > > and this: > > http://www.fsf.org/licenses/gpl-faq.html#LinkingOverControlledInterface > > http://lists.gnupg.org/pipermail/gnupg-devel/2000-April/010043.html > > > > Linus thinks that dynamic linking is ok, RMS doesn't, but gives > > an example boilerplate that says how dynamic linking can be ok > > w/ the GPL. It's definitely a grey area. > > Right, dynamic linking is a case where RMS would like the GPL to spread > the the closed-source binary, but I don't think he can legally do that. > > We do have that issue with our linking in of libreadline. We may adopt > libedit someday for that very reason. I was under the impression that the GPL only covers distribution, not use (as seems normal for copyright). In other words, as long as you don't ship readline with PostgreSQL you're fine. If the user wants to install it on their machine with readline linked in that's their problem entirely. Now, I think that people have tried to argue that if a library is the *only* implementation of the interface then it should be considered linked in because otherwise you're just using dynamic linking to get around the GPL. But since PostgreSQL doesn't depend on readline (it is optional after all) I don't see the issue. However, for the MySQL client library since the software strictly depends on that library, the fact that it's distributed as a seperate tarball does not absolve you of the GPL requirement. Obviously MySQL wouldn't have done their licence this way if they didn't think it was enforcable. Maybe they have themselves an exception or variation on the GPL? But it's still confusing. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > "All that is needed for the forces of evil to triumph is for enough good > men to do nothing." - Edmond Burke > "The penalty good people pay for not being interested in politics is to be > governed by people worse than themselves." - Plato pgp0.pgp Description: PGP signature
[GENERAL] Prevent from Deletion
Hi, I have tables that have default records that must not be deleted or modified. Is there an easy way to do this. Like setting a trigger on the Primary key value ? Alex ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Quetions on Joins
Ron, the idea is to provide a table where users can define filters. But it this table may be as well empty. Alex Ron Johnson wrote: On Sun, 2003-08-31 at 11:56, Alex wrote: Hi, I have a query where I want to filter out records from table_a if a field in table_a matches in table table_b. Basically table_b defines the filter. If table_b however is empty i dont get any results SELECT A.value_one FROM table_a AS A, table_b AS B WHERE A.value_two <> B.value_two; or SELECT A.value_one FROM table_a AS A, table_b AS B WHERE A.value_two <> B.value_two AND B.value_two NOTNULL; Only work if the there is a value in table_b. Could anyone tell me if there is a way to do that ? If the filter is empty, how can you filter anything? PS: I'd like to thank here persons who reply rather than sending the message per mail. What does that mean? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] how to group by a joined query?
suppose I've got two table: laser_uni=# \d t1 Table "public.t1" Column | Type | Modifiers +--+--- name | text | addr | text | laser_uni=# \d t2 Table "public.t2" Column | Type | Modifiers +-+--- name | text| len| integer | of | integer | and I want to use join to select out data and then group by one column, like this: laser_uni=# select t1.name, t1.addr, t2.name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t2.name; ERROR: Attribute t1.name must be GROUPed or used in an aggregate function seems the I must gorup all those fields: laser_uni=# select t1.name as t1name, t1.addr as t1addr, t2.name as t2name, t2.len, t2.of from t1 right join t2 on t1.name=t2.name group by t1.name, t1.addr, t2.name, t2.len, t2.of; t1name | t1addr | t2name | len | of +++-+ || henry | 2 | 4 || laser | 4 | 4 (2 rows) is it specification compliant or postgresql specific? Thanks Laser ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
