[SQL] Creating an aggregate function
Hi all, This e-mail is long because I am providing lots of details on the problem. It's an OpenACS module that we are porting so it'll have lots of users, you can be sure. I have this view that I am porting that is a UNION of two self OUTER JOINed views. PG does not support UNIONs in views, so we thought of creating our own aggregate functions to do the job. There are no examples for CREATE AGGREGATE in the docs, but we made it work, at least partially, but it explodes a little after the first row column. Here's the original Oracle query: create or replace view cs_n_sessions_day_user as select b.date_id, b.n_sessions_day, nvl(a.n_users, 0) as members, b.n_users as non_members from cs_historical_visits_grouped a,cs_historical_visits_grouped b where b.n_sessions_day = a.n_sessions_day(+) and b.date_id = a.date_id(+) and 1 = a.member_p(+) and b.member_p = 0 UNION select a.date_id, a.n_sessions_day, a.n_users as members, nvl(b.n_users, 0) as non_members from cs_historical_visits_grouped a, cs_historical_visits_grouped b where a.n_sessions_day = b.n_sessions_day(+) and a.date_id = b.date_id(+) and a.member_p = 1 and 0 = b.member_p(+); Here is our port of the cs_historical_visits_grouped view: create view cs_historical_visits_grouped as select date_id, n_sessions_day, not_null_integer_p(user_id) as member_p, count(browser_id) as n_users from cs_historical_visits group by date_id, n_sessions_day; not_null_integer_p is a function we created that basically does case when user_id is null then 1 else 0 end, but for some reason PG wouldn't take it in a view. Here's a helper view for our aggregate function: create view cs_hist_visits_grouped_txt as select date_id, n_sessions_day, member_p, n_users, member_p::varchar || ' ' || n_users::varchar as member_p_n_users from cs_historical_visits_grouped; And here are the aggregate functions: create function agg_if_member (integer, varchar) returns integer as ' declare a_int alias for $1; a_varchar alias for $2; member_p integer; num_members integer; begin member_p := substr(a_varchar, 1, 1)::integer; num_members := substr(a_varchar, 3)::integer; if member_p = 1 then return num_members; else return a_int; end if; end; ' language 'plpgsql'; create function agg_if_not_member (integer, varchar) returns integer as ' declare a_int alias for $1; a_varchar alias for $2; return_val integer; member_p integer; num_members integer; begin member_p := substr(a_varchar, 1, 1)::integer; num_members := substr(a_varchar, 3)::integer; return_val := 0; if a_int is not null then return_val := a_int; end if; if member_p = 0 and num_members > a_int then return_val := num_members; end if; return return_val; end; ' language 'plpgsql'; create aggregate num_members_or_zero ( basetype = varchar, stype1 = integer, sfunc1 = agg_if_member, initcond1 = 0); create aggregate num_non_members_or_zero ( basetype = varchar, stype1 = integer, sfunc1 = agg_if_not_member, initcond1 = 0); and our query was: select date_id, n_sessions_day, num_members_or_zero(member_p_n_users) as members, num_non_members_or_zero(member_p_n_users) as non_members from foobar group by date_id, n_sessions_day; Our fake table foobar had the same structure as our cs_hist_grouped_txt view has, and this was the dummy data we had inserted to play with: date_id | n_sessions_day | member_p | n_users | member_p_n_users -++--+-+-- 1 |500 |0 | 50 | 0 50 1 |500 |1 | 30 | 1 30 2 | 2000 |1 | 210 | 1 210 2 | 2000 |0 | 999 | 0 999 and our output was: date_id | n_sessions_day | members | non_members -++---+- 1 |500 |30 | 136394044 2 | 2000 | 136394612 | 136394612 the values for members is what we expected for date_id == 1, but the other ones are all goofy. Anybody has any ideas or worked with CREATE AGGREGATE before? Sorry about the long email and TIA. -Roberto Mello -- Roberto Mello, [EMAIL PROTECTED] - GNU/Linux Reg.User #96240
[SQL] Isolation and Concurrency in PG functions?
Hail PG Gurus, Can somebody please tell us about concurrency in PG functions? I am porting some VERY big functions to PG for a data warehousing system (that is GPL'd BTW) and it seems that the results of one portion of the function (e.g. a create table or a series of inserts) are invisible to the other parts which obviously causes the function (and following functions) to fail and is completely driving me nuts because I see the results when I do the queries interactively. What can I do about this? Is autocommit off in functions? How can I bring it on? This becomes worse because transactions are not supported in functions (anybody knows when will this be around?). Thanks, -Roberto Mello -- Roberto Mello, [EMAIL PROTECTED] - GNU/Linux Reg.User #96240 Computer Science - Utah State University USU Free Software and GNU/Linux Club, President http://fslc.usu.edu - http://www.brasileiro.net/roberto
[SQL] UNION in views
Hi all, I am part of the team that's porting the ArsDigita Community System (ACS), a toolkit to create community-oriented db-backed websites, from Oracle to PostgreSQL. We call the projet OpenACS. We are thinking of using PG 7.1b for the port (since it'll take some time, allowing PG 7.1 to mature), because of OUTER JOINs support and because of a message by Tom Lane that I read a couple days ago where he said that subselects in the from clause are now supported in the CVS version of PG. I was wondering if UNIONs in VIEWS will be supported too. Do you have a time frame for the release of 7.1b? Any additional comments on 7.1 features,drawbacks, caveats, etc, are appreciated. Thanks, -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu http://www.brasileiro.net/roberto
Re: [SQL] Requests for Development
Josh Berkus wrote: > > Tom, Bruce, Jan, etc.: > > As a PGSQL developer and business customer, I wanted to make some > public requests as to the development path of PGSQL. While, obviously, > you will develop the functionality *you* are interested in, I thought it > might be valuable to you to know what things would be most appreciated > (and please, list folks, speak up). I second all Josh's requests and I could add: - Procedures instead of just functions on PL/PgSQL (and maybe PL/Tcl). - Default values for PL/PgSQL functions/procedures. Thanks for the great work PG team. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Using Array-Values in subselect
Alvar Freude wrote: > > Hi, > > i want to create a linked structure of values. Each text has an id, an > array of children ids and the value itself. Looks like you want something similar to Oracle's CONNECT BY statement. There are some solutions to that. At OpenACS we had to deal with that so implemented something like what you described here. However, the methods described by Joe Celko is his book "SQL For Smarties" on chapters 28 and 29 (I think) are better and more robust. If you search for "trees" and related topics at the openacs.org and arsdigita.com's web/db web bulletin boards, you'll find several hits. -Roberto Mello P.S: I know this doesn't directly anwser your question, but points you to where to find the answer for yourself. -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Requests for Development
On Wed, 15 Nov 2000, Jan Wieck wrote: > > To put the ball back into your yard, I'd like to make a > request too. There seem to be alot people using PL/pgSQL > and/or PL/Tcl extensively. OTOH there are newbies again and > again asking for a good tutorial, programming examples and so > on. Writing a good tutorial doesn't require a good backend > developer, IMHO an experienced SQL-programmer would be the > better guy anyway. During the past 4 years I've heard over > and over that people would like to contribute their $0.05 if > they only could code in C. That's an area where nobody needs > any C experience. I have this on the way. I started creating such document a couple months ago when I was porting stuff from Oracle to PostgreSQL and stumbled on the few examples on the documentation. I'd be glad to finish it up, add more things to it and then put it somewhere for review, comments, suggestions, additions, etc. Part of this document will be on how to port Oracle PL/SQL to Postgres' PL/SQL and PL/Tcl. - Roberto Mello Utah State University - Computer Science USU Free Software and GNU/Linux Club - http://linux.usu.edu Linux para quem fala Portugues- http://linux.brasileiro.net Linux Registered User #96240
Re: [SQL] how many rows? [was Re: fetching rows]]
"Robert B. Easter" wrote: > If someone has done it a better way, I'd like to hear how. However, > sometimes it is possible to cache a count(*) value somewhere in the database > so it doesn't have to be found everytime - it depends on your database and > what the select is if you can store the count in advance somehow. I don't know how it was implemented (source code is available), but the PostgreSQL driver for AOLserver (a kick-butt web server for database-backed websites) has a function that does that. All I do after a SQL statement is: set rowcount [ns_pg ntuples] in my Tcl code and there it is. The driver is available at either http://www.aolserver.com or http://www.openacs.org (the latter has a more elaborate version of the driver). -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Persistent Connects (pg_pconnect)
Itai Zukerman wrote: > > Not sure if this is related, but under the non-threading Apache > server, you get 1 persistent connection per Apache process. I don't > think you can be sure which process gets the page with the connection > request, so if you have a limit of, say, 50 processes, and your server > is hit fairly often, eventually you'll see 50 connections even though > only a few simultaneous accesses to PHP/PostgreSQL are made. One way to work this out is to use a webserver that is fully multithreaded, with pooled connections to the database that can be shared among threads as needed. This yields much superior performance and saves a ton of resources. One example of such type of webserver that has been fully multithreaded and under heavy use since 1995, and has a PHP4 module is AOLserver, that powers mighty aol.com and digitalcity.com (with mere 32 K hits per second). AOLserver also has a built-in database abstraction layer and Tcl interpreter (with the option of caching compiled code of scripts), and a comprehensive API for building massively scalable dynamic websites. More info at http://www.aolserver.com -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] a script that queries database periodically
"Poul L. Christiansen" wrote: > > I was thinking of writing up a PHP script and put into crontab, which is > > somehow easier than a shell script, but PHP is an apache module, so I > > cannot execute PHP under crontab (it has to be executed in a browser > > right?). I guess a shell script is necessary. So, is it possible to > > call 'psql' and returning its query result and I can use sendmail to > > email the result? Any other idea? PHP does not have a scheduling facility? AOLserver (the web/application server that powers AOL) has had such facility (and many many others for db-backed websites) since 1995. ns_schedule_proc. http://www.aolserver.com -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [PHP-DB] Re: [SQL] a script that queries database periodically
Jason wrote: > > aolserver is a web/application server. PHP is a server-side scripting > language. Why exactly *should* it have a job scheduler? > > Some (such as myself) might also ask why should a web server have a job > scheduler, but that's a thread for a different list :) Because PHP is supposed to solve web development problems. And this is one of them. It's very useful. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] OpenACS
Edmar Wiggers wrote: > > Is there any PostgreSQL developer working on the OpenACS (PG version of the > ArsDigita Community Sytem) project? > > I have installed it and I am very much interested. There are a lot of small > bugs though, mostly related to Oracle->Postgres migration, and some which > are Postgres specific (queries that should work but don't). Hi Edmars, I am in the OpenACS team. Have you posted the bugs to our Software Development Manager (SDM) (http://openacs.org/sdm)? When we were doing the porting some queries might have slipped, and they're not found unless someone really uses the module, finds and reports them. OpenACS 4 is what we are actively working on right now. It's a much better piece of software than the 3.x series, but it does not have all the modules that 3.x series does, but we'll get there eventually. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] String function page incorrect?
Jonathan Ellis wrote:
>
> I'm trying to find the correct function that returns the location of a
> substring within a string. Looking at
> http://www.postgresql.org/docs/user/x2731.htm, it gives the Function name as
> "textpos" but in the Example column it uses "position". But neither one
> works!
>
> bf2=# select position('high', 'ig');
> ERROR: parser: parse error at or near ","
You need SELECT position('ig' in 'high');
lbn=# select position('ig' in 'high');
strpos
2
(1 row)
-Roberto
--
Computer ScienceUtah State University
Space Dynamics Laboratory Web Developer
USU Free Software & GNU/Linux Club http://fslc.usu.edu
My home page - http://www.brasileiro.net/roberto
Re: [SQL] I can be a BUG?
Tom Lane wrote: > > It kinda sounds like your wife created those tables in template1. > > Duplicating template1's contents into new databases isn't a bug, > it's a feature ;-) I've seen this behaviour too. Yesterday I pg_dumpall my 7.01 db compiled from scratch and loaded in PG 7.02 installed from Debian packages (I know a pg_dumpall wasn't needed, but I did it anyway). When I went into psql, I was some of my databases with all the pg_* tables. Why? Also, if I dropdb the database and createdb it again, it's as if I never deleted it. Is that normal/bug/feature? Why? -Roberto P.S: It's nice to see some Brazilians on the list :) I am from Brazil too. I hope Conectiva is promoting PostgreSQL down there. -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] I can be a BUG?
"Ross J. Reedstrom" wrote: > > When I went into psql, I was some of my databases with all the pg_* > > tables. Why? Also, if I dropdb the database and createdb it again, it's > > as if I never deleted it. Is that normal/bug/feature? Why? > > > > Ah, the Debian PostgreSQL package uses a different default convention with > psql than a from source compile: if you don't provide a database name, > debian psql will connect you to template1. On a from source compile, > it'll try to connect to a database that matches your username. Bit I did specify my database name. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Postgres closing the connection too fast with php4+apache
On Wed, Dec 13, 2000 at 10:55:03AM +0100, Bruno Boettcher wrote: > > despite this i still get sometimes the dreaded: > Warning: 45 is not a valid PostgreSQL link resource in > /home/bboett/www/fibu/includes/global.php on line 344 >Erreur durant la requete conn = Resource id #45 Pardon me, bout wouldn't this be more appropriate on a PHP list? -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] postgres
On Wed, Dec 13, 2000 at 04:44:55PM -0800, Josh Berkus wrote: > Mr. Daoust, > > You have reached the PostgreSQL SQL developers mailing list. We are > not PostgreSQL sales people, and we have no marketing information to > sell you. Please have a clue. Errr... forgive me, but maybe we could be help the PostgreSQL team by kindly directing people to their corporate website, so they can purchase services that are going to fund the project. As long as the project is happy I am happy. Just some thoughts. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Four Odd Questions
On Fri, Feb 02, 2001 at 04:37:34PM -0800, Josh Berkus wrote: > Folks, > > 1. Has anyone had experience with trying to link Informix's 4GL as a > procedural language extension for PostgreSQL? ANyone care to > speculate? I happen to have access to a couple of former Informix > employees ... Is that open source? I doubt it. > 2. Is there any documentation on the SQL changes being incorporated into > 7.1? We've talked about some of them on this list, but I'm still not > sure what the syntax for "ALTER TABLE" will be, for example. http://www.postgresql.org/devel-corner/docs/ -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
[SQL] Contributing Documentation to PG
I finally came around to writing some extra documentation for PL/PgSQL (and maybe PL/Tcl) and how to do PL/SQL-to-PL/PgSQL porting. I downloaded the documentation sources, intending to use its nice configure/make scheme but I can't find the configure script as described in the current docs. Where can I find it? I know I can just use the Makefiles with a little tweaking, but I am wondering if I am missing something here. Thanks, -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
[SQL] PL/PgSQL FOR syntax
Hi, What's the correct syntax to access rows in a FOR loop? I'm writing a PL/PgSQL doc and seem to be making a mistake somewhere. I am referring to this: [<>] FOR record | row IN select_clause LOOP statements END LOOP; How do I access the rows within the for loop? row.field? Thanks, -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
Re: [SQL] Help Retrieving Latest Record
On Fri, Feb 16, 2001 at 09:41:09AM -0500, Steve Meynell wrote: > > I know this doesn't work but I need something like it. > or something like > > select * from basket where max(date) and fruit='Apples'; > This would yield me: > 15Apples July 20, 1999 Maybe you mean min? This is the earliest date you had in the set. Assuming the Date column is of datetime/timestamp type: test=# \d fruits Table "fruits" Attribute | Type | Modifier ---+---+-- fruit_id | integer | name | character varying(50) | date | timestamp | test=# select * from fruits; fruit_id | name| date --+---+ 1 | Orange| 2001-02-16 13:26:52-07 3 | Pineapple | 1999-10-04 00:00:00-06 2 | Apple | 2000-12-05 00:00:00-07 4 | Apple | 2000-07-01 00:00:00-06 (4 rows) test=# SELECT * FROM fruits WHERE name='Apple' ORDER BY date DESC LIMIT 1; fruit_id | name | date --+---+ 2 | Apple | 2000-12-05 00:00:00-07 (1 row) Look at the documentation for the ORDER BY and LIMIT clauses on the PG docs. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
[SQL] PL/SQL-to-PL/PgSQL-HOWTO beta Available
Hi all, I finished the beta version of my PL/SQL-to-PL/PgSQL-HOWTO last night and put it in http://www.brasileiro.net/roberto/howto . It explains basic differences between Oracle's PL/SQL and PG's PL/PgSQL and how to port apps from one to the other. It also includes my instr functions that mimick Oracle's counterpart (they are handy). Please take a look and send me ([EMAIL PROTECTED]) any suggestions, criticism, etc. I am almost done writing my PL/PgSQL documentation that hopefully will make into the PG doc tree. -Roberto -- Computer ScienceUtah State University Space Dynamics Laboratory Web Developer USU Free Software & GNU/Linux Club http://fslc.usu.edu My home page - http://www.brasileiro.net/roberto
[SQL] Passing a table to PL/pgSQL
Hi,
I am trying to learn how can I pass a table to PL/pgSQL functions and
what can I do with them. I have heard somethings about these things, but
I'd like to know for certain.
Especifically:
- Can I pass a table to PL/pgSQL?
- Can I pass a parameter that's a member of a table (e.g.
users.user_id%TYPE)?
- Can a parameter have a default value?
Thanks,
-Roberto Mello
P.S: I searched lots in the PG search engine, but couldn't find
anything.
--
Computer ScienceUtah State University
Space Dynamics Laboratory Web Developer
USU Free Software & GNU/Linux Club http://fslc.usu.edu
My web site: http://www.brasileiro.net
Re: [SQL] quotes in pl/pgsql
On Thu, Mar 08, 2001 at 05:54:38PM -0500, Najm Hashmi wrote: > Hi all, I just want to know how to put quotes around a string. Is there a > function to do so? > If not how can I escape a single quote. Others have answered how to quote a single string. I wrote an entire section on quotes on the PL/SQL-to-PL/pgSQL-HOWTO. It's called "Quote me on that" and mentions several cases on quote usage in PL/pgSQL and what to do about it. http://www.brasileiro.net/roberto/howto -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer DOS = Damned Old Software ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Contribute to the PL/pgSQL CookBook !!
I have started the "PL/pgSQL CookBook" project. The goal is to create a cookbook of PL/pgSQL functions that will be catalogued and made available for others to use and learn from. Come to http://www.brasileiro.net/postgres and contribute your own PL/pgSQL (or PL/Tcl, PL/Perl) function or trigger! This will help many Postgres users, both novice and experienced, to use its procedural languages. The CookBook has several sections, and you can add your own. No login is required, just come and contribute. Once again http://www.brasileiro.net/postgres Oh, did I mention that you get your own "PostgreSQL Powered" button when you contribute a function/trigger? :) -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer Pimentus annus alter, refrescum est. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Contribute to the PL/pgSQL CookBook !!
I have started the "PL/pgSQL CookBook" project. The goal is to create a cookbook of PL/pgSQL functions that will be catalogued and made available for others to use and learn from. Come to http://www.brasileiro.net/postgres and contribute your own PL/pgSQL (or PL/Tcl, PL/Perl) function or trigger! This will help many Postgres users, both novice and experienced, to use its procedural languages. The CookBook has several sections, and you can add your own. No login is required, just come and contribute. Once again http://www.brasileiro.net/postgres Oh, did I mention that you get your own "PostgreSQL Powered" button when you contribute a function/trigger? :) -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer DOS = Damned Old Software ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] drop table in PL/pgSQL
On Fri, Mar 23, 2001 at 09:52:56AM -0800, Jie Liang wrote: > > You cannot CREATE|DROP ALTER table in PL/pgSQL, > in general, plsql can only take DML(i.e. SELECT| > INSERT|UPDATE..) You can't? I just did (on PG 7.1). AFAIK, you _can_ CREATE/DROP, but you can't roll back. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer "Carrier detected." Go to the dentist... ---(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] Help
On Sat, Mar 24, 2001 at 10:50:31PM -0800, Mohamed ebrahim wrote: > Hi, > > I am a user of postgresql. I want to know that it > is possible to call a jsp file in postgre > command.Please help me to know how to call a jsp file. You need a Servlet container to process jsp files and PostgreSQL does not have a Java virtual machine. But you can use PostgreSQL from your Java program by using JDBC. Look for Jakarta/Tomcat for an open source servlet container. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer ---(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] Oracle -> Postgresql migration
On Wed, Mar 28, 2001 at 01:24:11PM -0500, Douglas Brunton wrote: > Hello, > I am currently in the process of attempting an Oracle 8i to Postgresql 7.0.3 >migration. The table conversions were pretty straight forward, and I have all of the >tables ported over (with some minor datatype conversions). The stored procedures are >a different case altogether. I am wondering if anyone on the list has >pointers/conversion utilities for undertaking this task. Any information is welcome. I wrote a "Porting from Oracle PL/SQL" document that was added to the PL/pgSQL documentation in 7.1. It has lots of useful info on how to port to PL/pgSQL. You can find this document under the Programmer's manual on the PG 7.1 documentation (development docs). This week I will change that document to become more generic and add information on how to change some Oracle'isms (e.g. nvl, decode, etc.) to PostgreSQL, sticking to the standard where possible. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club|--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Web Developer Backup not found: (Q)uem mandou usar o Stacker? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Calling functions inside a function: behavior
On Mon, Apr 02, 2001 at 08:50:32AM -0300, Edipo Elder Fernandes de Melo wrote: > > and, for my surprise, it take minutes to run. I read the documentation > and I didn't found any coment abou this behavior. Can anyone explain this? Cool, one more Brazilian in the list :) I don't see any difference between calling the functions with a select or a perform, except that with perform the return value is discarded. What version of PG are you running and what are those functions written in (PL/pgSQL, PL/Tcl, PL/Perl)? Can you post a snippet of the functions here? -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer TOFU -> Text Oben Fullquote Unten ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Sorting and then...
On Mon, Apr 09, 2001 at 07:22:52PM -0400, Wei Weng wrote: > And I want to get the names of the largest 10 "id"s. How can I do that in > sql? What do you mean by "largest"? Largest id? "largest" text string? If it's the id you can do: select max(id) from ; -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Backup is for whimps! ---(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] Timezone conversion
Hi, How can I do timezone conversions in PG? I looked on the docs and couldn't find how. I want to find the current time in another timezone. Thanks, -Roberto P.S: This type of function would be excellent on the cookbook (www.brasileiro.net). -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Ad astra, per ardua nostra. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function overlaps_interval
On Thu, Apr 12, 2001 at 11:23:28AM -0700, Josh Berkus wrote: > Folks, > > I wrote this PL/pgSQL function for my current project, and thought it > would be generally useful. An expansion of the builtin > overlaps(dt1,dt2,dt3,dt4) function, this function returns the interval > of time for which the two datetime ranges overlap. > > Roberto, please include this in your online PL/pgSQL function library. Done! Thanks Josh! Date/Time manipulation functions are now our most popular category. How about you? Do you have a (SQL|PL/pgSQL|PL/Perl|PL/Tcl|PL/Python) function that you are proud of? A function that makes your life easier? Then waste no more time! Point your browser to http://www.brasileiro.net/postgres submit your recipe and join the CookBook Hall of Fame! You get your own awesome-cool "PostgreSQL Powered" button! We've been getting many hits from techdocs.postgresql.org, which shows that many are going to techdocs looking for solutions to common problems. Very useful. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Bad command or file name. Go sit in corner. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Debian Package problems
Does anybody know what's going on with the Debian 7.1 RC4 packages? It will not let me create any users or even talk to the backend. If I try to create a user (as user postgres), it asks me for a password, but it's not the UNIX password because I tried that (I changed the password right after I installed the packages). If I try to connect through psql, it asks me for the password again. It looks like it's using password auth instead of ident auth, which used to be the default. There's nothing pointed in the packages web pages, a caveat or anything. I've looked. Thanks, -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Ad astra, per ardua nostra. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Debian Package problems
On Fri, Apr 13, 2001 at 09:00:45AM -0600, Roberto Mello wrote: > If I try to connect through psql, it asks me for the password again. > It looks like it's using password auth instead of ident auth, which used > to be the default. > There's nothing pointed in the packages web pages, a caveat or > anything. I've looked. Okay, I feel dumb now. It looks like Oliver Elphick (the package maintainer) changed the default authentication methods. Before local users were "trust" now they are "password". Thing is, if it's password, how do you know the password for user postgres? This (a note) should be in the package pages, or at least told the user during upgrade. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Go FORTH and C PASCAL play COBOL with an APL. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Debian Package problems
On Fri, Apr 13, 2001 at 11:38:18AM -0400, Stan Brown wrote: > I don;t have a clue. But I also am having fits with the Debian package for > "stable" It installs fine, and then I su to postgres. At that point in time > psql will connect, but createuser fails :-( > > I would be interested in what yu find out. I changed /etc/postgresql/pg_hba.conf so that local users are "trust" again (instead of password). You are looking for this line (already changed): localalltrust That's probably not the safest way, but now that I can do something (because postgres can actually talk to the DB), I will change it back to password. > Windows 98: n. > useless extension to a minor patch release for 32-bit extensions and > a graphical shell for a 16-bit patch to an 8-bit operating system > originally coded for a 4-bit microprocessor, written by a 2-bit > company that can't stand for 1 bit of competition. :) Remarkably accurate! -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer C program run. C program crash. C programmer quit. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Using Random Sequence as Key
On Sun, Apr 15, 2001 at 10:58:40PM -0300, Bernardo de Barros Franco wrote: > I wanted to index a table by a random key. Exemplifying, when a insert is > made, the id value is automatically filled with a random number between > 1 and 9. I'm pretty new in pgsql so I could use a howto or Bernardo, Do you really need the keys to be random numbers? Can't the keys be sequential numbers, like 1,2,3,4...? That'd be a lot easier to make unique, and as far as the user is concerned, it'll be random for him/her. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Oh my GOSH! A Prompt!!! HELP A PROMPT ! HELP ! ---(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] Re: DB porting questions...
On Tue, Apr 17, 2001 at 11:45:08AM -0400, Vivek Khera wrote:
> I'm just starting, but I've got two questions. I've found some
> scripts out there that claim to do the conversion of the SQL create
> commands, but none does the right thing it seems.
Please help better these scripts then. That way you're helping
everybody, including yourself (with gained experience).
> I've now found out how to handle the timestamp for insert times and
> how to do auto-increment fields.
> My unsderstanding of MySQL's enum type is to use something like this
> in postgres:
>
> owner_status varchar(9) check
> (owner_status in ('pending','active','suspended'))
> NOT NULL default 'pending',
That's standard SQL, which PostgreSQL supports. You could use this
same statement in Oracle, or other compliant DBs.
> Currently in MySQL I have this:
>
> owner_features set('premium','haveccinfo') default NULL,
>
> for example. Some other fiels may have about 20 such values, and
> MySQL lets me keep these in 3 bytes as a bit-field behind the scenes.
MySQL is helping you get into trouble by giving you a non-standard way
to do something for which there's a standard.
> >From what I see, my choice in Postgres is to store this as a
> comma-separated string and let my application work as before.
For columns with more than a couple values, I'd suggest normalizing
your tables. In the "owner_features" case above, you could do something
like:
create table owner_features (
feature_id serial
constraint owner_features_pk primary key,
feature varchar(30)
constraint owner_features_feature_nn not null
);
Then your table would just reference owner_features.feature_id. Much
cleaner, especially for tables with lots of cases.
> Does anyone have a script that actually handles properly doing auto
> increments with the SERIAL type, and does the set/enum conversions?
What do you mean by "propely doing auto increments"? What's the
problem you are having?
-Roberto
--
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
Linux: What Windows will NEVER BE!
---(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] Where are the 7.1 RPM's?
On Tue, Apr 17, 2001 at 04:30:43PM -0700, Lonnie Cumberland wrote: > Hello All, > > I have been using the 7.0.2 version of PostgreSQL which came with my Mandrake > Linux and now want to do a fresh install of the 7.1 version because I think > that there are many things fixed since then. > > Do anyone know where the 7.1 RPM's are so that I can install them? Have you tried postgresql.org? I am sure you can find it there :) It looks like it's bogged down with downloads now. You can get it from our mirror at http://fslc.usu.edu/downloads/pub -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer -°*'. (Explosive Tagline) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
On Wed, Apr 18, 2001 at 10:35:09PM +1000, Keith Wong wrote: > an existing connection that is no longer being used (persistent connections > do tend to have a lot of quirks tho) What quirks? I am developing a PHP now and using persistant connections. Coming from the AOLserver/OpenNSD world -- where ALL connections are persistant, pooled, and dstributed among threads -- I'd be interested in knowing the quirks of persistant connections in PHP. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I may be fat, but you are ugly, and I can diet... ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] maybe Offtopic : PostgreSQL & PHP ?
On Wed, Apr 18, 2001 at 04:44:36PM +0200, Mathijs Brands wrote: > I've written several applications with Apache/PHP. At first I was using > persistant connections, but I was soon forced to revert to normal db > connects. The problem is the number of connections opened. If you have > 50 Apache processes and 5 different databases, it is highly likely that > you will end up (if you leave your application running long enough) with > 250 persistant db links; not something pgsql really likes, unless you > configure it to correctly handle large numbers of db links. Apache/PHP > doesn't provide you with some way to pool connections :( Ah, I see the problem. When you mentioned there were problems I thought you were talking about the connections themselves. I am using PHP 4 under AOLserver/OpenNSD, which has been fully threaded from scratch (1994) so I hope this won't be much of an issue (but then PHP 4 still has threading problems AFAIK). Not using persistant connections is just too slow for me. It's instantaneous for AOLserver to generate a page, when the same page with the connection overhead in PHP takes A LOT longer. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Tetris tagline: @@ o@o @oo oo@ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Postgresql to Access
On Thu, Apr 19, 2001 at 03:43:30PM +0200, Mateusz Mazur wrote: > Hello. > > Could you help me? I have database in psql and my boss want to have this > base also is MS Access (only like client - main base will be psql). He wants > to use access like viewer to psql base. What should I do. Look at the article an http://techdocs.postgresql.org -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I know a good tagline when I steal one. ---(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] plpgsql
On Fri, Apr 20, 2001 at 04:58:02PM -0700, Jie Liang wrote: > > I 've a question about begin...end in plpgsql > does > sql stmts in > begin > > end; > will go one transaction? Read the documentation (programmer's guide). It's all there. It's to answer your questions that we take the time to write docs in the first place :) Short answer: everything in your function is executed in one transaction. BEGIN and END in PL/pgSQL are NOT the same as in the transaction semantics. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer If at first you don't succeed, destroy all evidence that you tried. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] How to encode and decode password in pgsql !!
On Wed, May 02, 2001 at 01:07:57AM +0530, Subhramanya Shiva wrote: > hi all > > how to store password details in a table. in encoded form > and how to decode it. > > in mysql ...we r having encoding and decoding for a password > security ... so how to do in pgsql... I usually do the encrypting in the client application (AOLserver, PHP), store the encrypted password, when the user comes back and enters the password I encrypt it and test it against what's on the database. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Thou shall not kill, unless it's for dinner! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Information passing, Perl, Unix and Postgresql Database
On Wed, May 09, 2001 at 02:38:14PM -0400, Issurdatt, Portia CECOM RDEC STCD SRC wrote: > I need to extract information from a database table, do another query on > that information, then pass only the new information to a new table using > postgresql database and Unix. You might be able to do it with subqueries. PostgreSQL 7.1 has subqueries in the FROM clause as well al regular sebqueries. Read the subqueries chapter of Bruce Momjian's PostgreSQL book for more info, or give us more information. The book is at http://www.ca.postgresql.org/docs/awbook.html -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer _ _ _ _ _ _ || < domino effect at work ---(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] Re: Escape Quotes
On Fri, May 11, 2001 at 01:17:41PM +1000, Keith Gray wrote: > > The problem is in ipqsql... What the heck is ipsql?? -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer In Borland you are never bored! ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] Re: [HACKERS] Problems in porting from Oracle to Postgres
This is more appropriate for the pgsql-sql list, so im forwarding it that way. The hackers list is for other purposes. On Fri, May 11, 2001 at 12:24:25PM +0530, Amit wrote: > > 1> There is a code in Oracle like > > Type Tstate is table of number(9) > index by binary_integer; > > To define a runtime table, basically it works like a array, How can it > be possible in Postgres SQL, > I have tried create temp table But it not works.. > Is there any way to use arrays. It'd be much easier to help you if you posted the function/procedure you're trying to port. Just one line is harder. > 2> There is one function in Oracle Executesql '...' to execute > and what i got in Postgres is Execute immediate '.' > But it is giving error at Execute. Again, you're giving way too little detail. What error? What are you trying? Without this, it's very hard to help. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Cannot open CATFOOD.CAN - Eat logitech mouse instead (Y/n)? ---(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] Postgres function library
On Fri, May 11, 2001 at 08:48:01AM +0200, Lajtos Mate wrote: > Hi all, > > I read the post from Josh Berkus where he (at the end of the message) > mentions a 'function library'. > Is that library available for any developer or it's a proprietary one? > If it's public, can I post functions there? It's a "cookbook" of functions that we are trying to gather. Each function submitted has its own license specified by its author. Most of them are licensed under the GPL or BSD. Many are public domain. And yes, PLEASE post functions there. The more we have, the better. I am going to improve the site in a few days. http://www.brasileiro.net/postgres -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer As easy as pi=3.14159265358979323846264338327950288419716939937511 ---(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] Is this possible?
On Tue, May 08, 2001 at 09:16:56PM -0400, Wei Weng wrote: > I have a table that has a serial for primary key. Is it possible to get > the new available primary key right after I insert a row of new entry? Yeah. Se the documentation on triggers. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer "Data! I thought you were dead!" "No, Sir. I rebooted." ---(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] any proper benchmark scripts?
On Wed, Apr 18, 2001 at 07:00:24PM -0700, Clayton Cottingham aka drfrog wrote: "Benchmarks" comparing MySQL to anything else usually make the worst and lowest denominator compromise (which obviously favours MySQL). - What table types did you use on MySQL? Did you use the BDB (or whatever the transaction tables are called) or ISAM tables? If you didn't use transactional tables, it's not a fair comparison. - In PostgreSQL, did you do the inserts in a single transaction block or just did what MySQL users usually do because they know what a transaction is, and left every INSERT on its own (effectively putting each on its transaction block)? If you didn't put the inserts into a single transaction block, then it's not a realistic test because that's not what you would do in real life in a real database system (pretty much anything besides MySQL and Access). > > correct me if im wrong but if fast_db.pl is > working right > first set is insert > second set is select Speed is good. But it's not everything. You should do it right, then make it fast. MySQL implementors don't care about doing it right. They just care about making it fast. My filesystem is much faster than the MySQL filesystem (that's what it is). UDP is faster than TCP, but TCP is better and more reliable. A skydiver without a parachute will always fall faster than the one with parachute. The skydiving school will say "we make your fall more exciting, and much faster than our competitors." and the dumb skydivers will reply "well, if you make my fall faster, then of course you're the best". Which one will survive the fall? Comparing MySQL to PostgreSQL, Oracle or any other real database is like comparing DOS with UNIX, or Emacs with an orange. -Roberto Mello -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I wonder what this button does? *&^(&^)#@$*&_% NO CARRIER ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Exp/Imp Problems...
On Tue, May 15, 2001 at 08:22:36PM +0530, ameet wrote: > > We are trying to export a tablespace 'XYZ' from an Oracle8i Database server to a >Oracle 9i Application Server, both residing on different machines. > > As far the Export is concerned,we are succesfully able to create the required >'abc.dmp' file on the 8i server. > > We then copy the 'abc.dmp' file and the 'XYZ.DBF'(approx. 150 MB) to the machine >where the 9i server is located. Heh? I think you asked this on the wrong list. This list is for help with the PostgreSQL database. For Oracle support you should look for an Oracle forum/list or call their support. http://www.arsdigita.com/bboard (web/db forum) has lots of Oracle users. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer "Call it a hunch." -- Quasimodo ---(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] Give me a suggestion 'START WITH .. CONNECT BY'.
On Tue, May 15, 2001 at 05:40:32PM +0900, ?$B;3$5$s wrote: > Hello! > I try to translate a database to PostgreSQL from ORACLE, but have a regret > over a function '... START WITH .. CONNECT BY ...'. > This function is descrived in a sql sentence for ORACLE, and I could not > find in PostgreSQL. > For example >SELECT LEVEL, COMP_ITM, COMP_NAME, COMP_AMT FROM PSFILE > START WITH PRNT_ITM = 'A0010' > CONNECT BY PRIOR COMP_ITM = PRNT_ITEM; > There's no equivalent for the Oracle tree extensions in PostgreSQL. Neither LEVEL, START WITH or CONNECT BY PRIOR. There's no easy way out of this AFAIK. You'll have to re-write your code in a way that allows you to do this query in a SQL92 way. In Joe Celko's "SQL For Smarties" he talks about a nifty algorithm that you can use to do this. The OpenACS folks used that approach to port Oracle's CONNECT BY to PostgreSQL, so you could probably ask them over at openacs.org/bboard. -Roberto P.S: I plan to add this to my expanded "Porting From Oracle" chapter of the documentation. -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Air conditioned environment - Do not open Windows. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Large Objects Documentation
I couldn't find the documentation for dealing with Large Objects on the documentation. Any pointers? The only info I found on Large Objects was on the programming guide, but I'm not looking to write C functions with largo objects. Thanks, -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Be a pessimist. A pessimist is never disappointed. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] PGAccess/pgplsql Blues
On Fri, Jun 01, 2001 at 09:30:40AM -0700, Josh Berkus wrote: > > When I edit some of my more complex plpgsql functions using PGAccess, > the functions break and cannot be made to work again except by reloading > them from PLSQL/text. All attempts to use the function after PGAccess > editing are met with: 'Parse error at or near ""' IIRC, pgaccess does quote-escaping for you, so if you try to write "standard" PL/pgSQL (escaping single quotes), it'll barf this error. Just something to check. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I'm only a droid and not very knowledgeable about such things. ---(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] php-nuke
On Wed, May 30, 2001 at 11:14:38PM +0530, Sharmad Naik wrote: > hi, > I wanted to know that does postgresql database support php-nuke This is a php-nuke question. Head to their website and it should be stated there somewhere. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer I wonder what this button does? *&^(&^)#@$*&_% NO CARRIER ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Tutorial : using foreign keys, retrictions etc
On Sat, Jun 02, 2001 at 06:49:55PM -0400, Mario Bittencourt wrote: > Hi, > > I've decided to switch from mysql to postgresql as my database server. Good for you :) > procedures, views) and I'd like to know if there is some tutorial/snippet > of code regarding such features specially the use of foreign keys and > referetial integrity. You might want to look at http://techdocs.postgresql.org. IIRC, a script to "port" MySQL code to PostgreSQL has been included in the contrib section of 7.1.x. For examples of functions, head to http://www.brasileiro.net/postgres -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Bad command or file name. Go sit in corner. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] PL/PGSQL
On Tue, Jun 05, 2001 at 08:18:03AM +0200, Andrzej Roszkowski wrote: > Hi! > I can't find any info about possible data types that can be returned from > procedure. I want to return tuple. When I use OPAQUE as a return type I > get error message that OPAQUE can be only returned from trigger. It is > possible to do this without triggers? A little RTFM'ing wouldn't hurt. Currently you can't return tuples from "regular" PL/pgSQL functions. It heard this functionality will make it in 7.2. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Death to all fanatics!!! ---(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] Better Archives?
On Mon, Jun 18, 2001 at 08:55:34AM -0700, Josh Berkus wrote: > > I'd like to stop doing this, and I'm sure that some newbies, directed to > the archives, give up on Postgres entirely for something with a better > online knowledge base. I think so too. > Is there anything we can do about this? It seems tailor-made for a > PostgreSQL app ... I volunteer for design/SQL if someone can find a PHP > or Java coder for the interface. I think this has already been done, but I don't know if it has been linked from the man pg.org site. It's at fts.postgresql.org -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Windows? HA! CD\WINDOWS. DELETE *.* AH! Thats Better ---(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] About primary keys.
On Tue, Jun 19, 2001 at 02:10:16PM +0200, David BOURIAUD wrote: > Hi the list ! > Is there a way to get in system tables all the primary keys of a table ? There's a recipe that is related to that in my Postgres CookBook that you could adapt to your needs: http://www.brasileiro.net/postgres/cookbook/view-one-recipe.adp?recipe_id=36 -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer TAGLINE NO ESPELHO ¤ OHLEPSE ON ENILGAT ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Changing PL/pgSQL triggers
On Thu, Jun 21, 2001 at 01:30:39PM -0400, James Orr wrote: > Hi, > > What's the easiest way to modify or view a function written in PL/pgSQL? I've been >using pg_dump to get the original function, then dropping and creating the function >and trigger after making a change. Is there an easier way? pgAcess should make it easier. It lets you look at the definition and lets you modify/drop/recreate it. If you're on windows, you might want to look a pgAdmin (available at greatbridge.org). -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer *> -| <- Tribble Archery ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] PL/TclU
I've heard quite a bit about PL/TclU, but what's the difference between that and regular PL/Tcl?? Thanks, -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |------+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer The pizza at the neigbors table has always MORE chesse. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] CREATE TYPE function examples
I'm looking for some examples of how the functions for CREATE TYPE should be. There are none in the documentation, so I was hoping to find someone here that could help me. For example, the documentation has this example: CREATE TYPE box (INTERNALLENGTH = 8, INPUT = my_procedure_1, OUTPUT = my_procedure_2); What would the functions my_procedure_1 and my_procedure_2 look like? Thanks, -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer YES!! eh, NO!!! oh, well MAYBE ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] CREATE TYPE function examples
On Tue, Jul 10, 2001 at 06:43:59PM +0200, Peter Eisentraut wrote: > > http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/xtypes.html Oh. So you have to write a function in C to use CREATE TYPE? > This seems to be outdated regarding the fmgr update, though. (Hint, > hint...) How so? Would you please elaborate? I'm note familiar with the update. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Politically correct? I'm not even ANATOMICALLY correct! ---(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] Group by date_part
On Tue, Jul 10, 2001 at 08:04:55PM +0100, Graham Vickrage wrote:
>
> The statement I have only selects the count if there is at least 1 order for
> a particular day, which make sense.
>
> I however need a count of 0 for days that don't have any. Can anyone help?
>
> SQL:
>
> SELECT date_part('day', date), count(*)
> FROM client_order WHERE (date >= '01/05/01' AND date < '01/06/01') AND
> status = 'Processing'
> GROUP BY date_part('day', date);
Didn't look very hard, but can't you just add a:
CASE WHEN count(*) > 0 THEN count(*) ELSE 0 END AS count
-Roberto
--
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
HELP! My hard drive crashed & I can't boot up!
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Re: [INTERFACES] distinguishing different database connections
On Tue, Jul 03, 2001 at 11:07:35AM -0500, Manika Dey wrote: > > Hi, > Is postgresql database available for WindowsNT platform. Yes. Download CygWin at http://www.cygwin.com and read the documentation in postgresql.org regarding installation on Windows. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Dente lupus, cornu taurus petit. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Get the tables names?
On Thu, Jul 19, 2001 at 11:04:40AM +0200, Magnus Landahl wrote: > Hi everybody! > > Is it possible to get the names of all tables in the database with a sql > query?? SELECT tablename FROM pg_tables WHERE tablename NOT LIKE '%pg_%'; The "NOT LIKE" part is to avoid getting the PostgreSQL internal tables in the output. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer *** TURN OFF YOUR SYSTEM NOW !!! *** ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[SQL] Converting epoch to timestamp?
I searched the docs for function to convert epoch to timestamps but couldn't find any. Are there any? Thanks, -Roberto -- +----| Roberto Mello - http://www.brasileiro.net |+ Computer Science, Utah State University - http://www.usu.edu USU Free Software & GNU/Linux Club - http://fslc.usu.edu Space Dynamics Lab, Developer http://www.sdl.usu.edu [<<] [>] [>>] [o] [||] [|>] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [SQL] Converting epoch to timestamp?
On Wed, Aug 01, 2001 at 09:28:39AM +0100, Richard Huxton wrote:
> Hi Roberto - long time no see.
Hey Richard. Yeah. Summer classes and summer jobs :-) I have to finish
my expanded "Porting From Oracle" thingy.
>
> richardh=> select '1970-01-01'::date + '996654342 seconds'::interval;
> ?column?
>
> 2001-08-01 08:25:42+01
> (1 row)
Duh! Guess I could have thought of that.
Thanks a lot.
-Roberto
--
+| Roberto Mello - http://www.brasileiro.net |+
Computer Science, Utah State University - http://www.usu.edu
USU Free Software & GNU/Linux Club - http://fslc.usu.edu
Space Dynamics Lab, Developer http://www.sdl.usu.edu
Veni, Vidi, VCR - I came, I saw, I videotaped it
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Checking for table existence
On Fri, Sep 14, 2001 at 06:58:29PM +, Julester wrote: > Hi everyone. In my old SQL Server days, I used a command such as "IF > exists(select name from sys_objects where name = 'xyztable')" to check if a > table existed before creating it with a standard CREATE command. I looked > in the PostgreSQL documentation, but for the life of me, I can't find an > equivalent. I can view if the table exists by doing a select against the > meta-data tables, but what about the IF statement ? Any help would be > greatly appreciated. Thanks. You can have psql output its internal queries and that will give you some insight: roberto@brasileiro:~/documents/pictures$ psql -e foobar Welcome to psql, the PostgreSQL interactive terminal. ... foobar=# \d blah * QUERY * SELECT relhasindex, relkind, relchecks, reltriggers, relhasrules FROM pg_class WHERE relname='blah' * * QUERY * SELECT a.attname, format_type(a.atttypid, a.atttypmod), a.attnotnull, a.atthasdef, a.attnum FROM pg_class c, pg_attribute a WHERE c.relname = 'blah' AND a.attnum > 0 AND a.attrelid = c.oid ORDER BY a.attnum * * QUERY * SELECT substring(d.adsrc for 128) FROM pg_attrdef d, pg_class c WHERE c.relname = 'blah' AND c.oid = d.adrelid AND d.adnum = 1 * Table "blah" Attribute | Type | Modifier ---+--+--- something | timestamp with time zone | default 'now' name | character(50) -Roberto -- +| Roberto Mello - http://www.brasileiro.net |+ Computer Science, Utah State University -http://www.usu.edu USU Free Software & GNU/Linux Club -http://fslc.usu.edu Space Dynamics Lab, Developer-http://www.sdl.usu.edu OpenACS - Enterprise free web toolkit-http://openacs.org Blood is thicker than water, and much tastier. ---(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] can't update 'c:\windows'
On Mon, Oct 22, 2001 at 05:14:32PM +0800, guard wrote: > dear all > > IF update table set field='c:\windows' > but > > c:\windows -> c:windows Errr. Some context please? My crystal ball is still getting fixed :) -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Windows is not the problem. Ignorance is the problem. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Primary key with oid + name : error, which solution ?
On Wed, Oct 31, 2001 at 10:06:22AM +0100, St?phane Chomat wrote: > I create two table repertory and person. And i have an error : > > > CREATE TABLE repertory (name_rep name, attribut text[], PRIMARY > KEY(name_rep)); > > CREATE TABLE person (nam_rep repertory, name_pers text, url text, > eadr text, tel text, attribut text[], PRIMARY KEY(name_pers,nam_rep)); > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index > 'person_pkey' for table 'person' > ERROR: DefineIndex: type repertory has no default operator class Unless it's some OO extension in PostgreSQL that I don't know about, I have never heard of the type "repertory" and "name". I don't think you can use your table name as a data type. BTW, you should also stay away from arrays (IMHO), since they are non-standard. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer NY cops go bar-hopping; LA cops go night-clubbing. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] PL/pgSQL syntax for strings
On Thu, Nov 01, 2001 at 04:48:29PM +, Edward Grabczewski wrote:
>
> DROP FUNCTION threedpoint (float,float,float, float,float,float);
> CREATE FUNCTION threedpoint (float,float,float,float,float,float)
> RETURNS text
> AS 'DECLARE
> x1 ALIAS FOR $1;
> y1 ALIAS FOR $2;
> z1 ALIAS FOR $3;
> x2 ALIAS FOR $4;
> y2 ALIAS FOR $5;
> z2 ALIAS FOR $6;
> BEGIN
> INSERT INTO rtest(xz,yz,xy)
> VALUES ( \'(0,2), (1,3)\',
> \'(1,2), (2,3)\',
> \'(0,1), (1,2)\');
> RETURN null;
> END;'
> LANGUAGE 'plpgsql';
>
> SELECT threedpoint(100,200,300,400,500,600);
Why do you pass all those variables to the function if you don't use them?
It doesn't make any sense to me.
As the documentation for PL/pgSQL clearly states with several examples,
you have to double the quotes in strings>
INSERT INTO rtest(xz,yz,xy)
VALUES (''(0,2), (1,3)'',
''(1,2), (2,3)'',
''(0,1), (1,2)'');
I don't know if the \' way of escaping quotes works. I never tried within
a PL/pgSQL function. My guess is that it doesn't.
-Roberto
--
+| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net
http://www.sdl.usu.edu - Space Dynamics Lab, Developer
My inferiority complexes aren't as good as yours.
---(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] CHECK problem really OK now...
On Mon, Sep 24, 2001 at 03:23:13PM -0400, Jan Wieck wrote: > > It help's saving life! Using the victims for that purpose > isn't abuse. It is turning grief, anger and sadness into > help and hope. > > Let blood become "Open Source". Give it for free and you'll > get plenty of it when you need some. I couldn't agree more! -Roberto -- +| Roberto Mello - http://www.brasileiro.net |+ Computer Science, Utah State University -http://www.usu.edu USU Free Software & GNU/Linux Club -http://fslc.usu.edu Space Dynamics Lab, Developer-http://www.sdl.usu.edu OpenACS - Enterprise free web toolkit-http://openacs.org TAFB -> Text Above Fullquote Below ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL-Programmer tool and field%type support
On Fri, Oct 05, 2001 at 03:37:14PM +0200, Mourad EL HADJ MIMOUNE wrote: > Hi, > I'm looking for PL/SQL programmer tool. I wanted to use SQL-Programmer but > it > doesn't support Postgres data base. > Please can sommeone suggest me an other tool wich can replace this one or > explaine me how we can use SQLProgrammer with Postgres if it is possible. I don't know such tool. The documentation has some advices on tools/ways to program in PL/pgSQL. > I have an other question about the use of %type and alias in PL/SQL. So the > excution of functions containing varibales of type maytable.field1%type > prompts parser errors. > error on the creation of the function : > create function test (int) returns maytable.field1%type as' ... This syntax is not supported in PG 7.1. AFAIK, it will be supported in PG 7.2. You can use function overloading if you need the same function to return different types. -Roberto -- +| http://fslc.usu.edu USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net http://www.sdl.usu.edu - Space Dynamics Lab, Developer Information is the greatest weapon of power to the modern wizard. ---(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] graphical interface - admin
On Mon, Jul 01, 2002 at 02:29:27PM +0300, Oleg Bartunov wrote: > > I tried 1.2.4 but didn't found how to configure tora to work > with postgresql. Tora is very nice. I'm using 1.3.5 with PG 7.2 (Debian). I had to compile the PostgreSQL Qt 3 module for it to work. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer * * * <- Tribbles § § § <- teenage mutant ninja tribbles ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] pl/pgsql capabilities?
On Mon, Jul 01, 2002 at 01:43:29AM +0200, Ferenc Engard wrote: > Hi all, > > A long time ago (at version 6.3.2 or what :) I have asked, but maybe > since it changed: can I use the procedural languages (any of them) to > return a table- (or view-) like output, just like in Interbase, for > example? E.g., if I have a metamodel and I want to write functions what > perform complex computations and queries, can they return the result > (which can be of many rows) to the client? As of PG 7.2 you can, by returning a cursor: http://developer.postgresql.org/docs/postgres/plpgsql-cursors.html -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer I wonder if Singapore has any computer pirateers or hackers... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] pl/pgsql capabilities?
On Tue, Jul 02, 2002 at 01:37:08AM +0200, Ferenc Engard wrote: > > If I understand well, I can create a cursor for a SELECT statement, and > return that cursor. Well, it is better than nothing, but I fear it is > not flexible enogh for me. Maybe not for the solution you have right now, but you most likely can rework things a bit to make it work. > Here is an example from an interbase app. I have to declare that this is > not my program, and I do not know IB, so forgive me if I say silly > things... > > CREATE PROCEDURE VIEW_1_1 > ( > SZEMPONTID INTEGER, > PARENT CHAR(10) CHARACTER SET WIN1250, > ELNEVEZESTIPUSID INTEGER, > RENDSZERKOD CHAR(16) CHARACTER SET WIN1250 > ) ... > The point is not what this proc does (in summary, it gets all the childs > with their properties of a parent in a tree structure), but it cannot > gather all the information with just one select, e.g. it gets some data > from another (nontrivial) stored procedure. > > As I see, that 'suspend' command gives back the actual row (and the > control) to the caller until it fetches the next row from this proc's > return value. Do I interpret correct that this stored proc returns a > cursor with structure described in the 'RETURNS' part, and which is not > linked to a SELECT statement? > > Can I do something similar in PG? If not now, maybe in the (near) > future? Not in the way you currently have, as far as I could understand it. I didn't read the procedure very attentively because it's horribly written in all caps, and my eyes started scrambling the letters half way through. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer "Data! I thought you were dead!" "No, Sir. I rebooted." ---(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 do i return a dataset from a stored procedure
On Fri, Jul 05, 2002 at 01:32:33AM -0700, teknokrat wrote: > I can't figure out how to return a group of rows from a function > written in plpgsql (or plsql for that matter). The only way i have > seen it done is with a setof table return value in sql. But since the > query was a single select anyway i don't see the point of this. Is > there a way of creating a temporary table in a procedure and using it > as the output? Search the list archives (through groups.google.com - it's comp.databases.postgresql) or see the developer docs for PL/pgSQL. You can achive that effect by returning a cursor in PG 7.2 (the section on returning cursors is ommitted from the current docs in www.postgresql.org/idocs, so you need to look at the developer docs - see www.us.postgresql.org). -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Go straight to the docs. Do not pass GO. Do not collect $200! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] newbie question
On Sun, Jul 07, 2002 at 11:59:51PM +0200, Mirco D'Angelo wrote: > Hi > > I am going to learn MySql 'cause I have to, but would it be better, or let's > say, more interesting, to learn postgressql? Is it newer, more common, etc.? PostgreSQL is certainly more interesting, more useful, more feature-complete (as far as Relational database servers go), more fun and what you could learn with PostgreSQL you could take to other database servers who are out to be real database servers, not the case with MySQL. AFAIK, MySQL is more common. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Why trying to DRINK and DRIVE, while you can SMOKE and FLY? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] manipulating the database in plsql
On Sun, Jul 07, 2002 at 11:40:19AM -0700, teknokrat wrote: > Is there any way to work with tables etc, in plsql? Can i get a get a Yes. > database handle to the local database? If not, is there any chance for > this to be implemented in the near future? I don't know what you mean. A PL/pgSQL function is already part of the database it belongs to. It doesn't need a handle. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer All true wisdom is found in taglines. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Editor for pgsql
On Mon, Jul 22, 2002 at 09:36:54AM -0300, Elielson Fontanezi wrote: > Hello Folks! > > > Maybe every DBA knows that there is a good shareware to editing > Oracle PL/SQL programs > named PL/SQL Developer. > I'd like to know if someone knows something near to this software > for Postgres pgSQL. Tora (tora.sf.net) has PL/SQL editing and supports PostgreSQL. Since Pl/pgSQL is very similar to PL/SQL, you can use Tora for PL/pgSQL editing very nicely. Tora is an overall very nice database administration tool. I use it with my Oracle and PostgreSQL installations all the time. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Microsoft has been doing a really bad job on their OS - Linus Torvalds ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Editor for pgsql
On Mon, Jul 22, 2002 at 05:41:39PM -0700, Josh Berkus wrote:
>
> I tried to install Tora, but the build blew up since I don't have Oracle
> installed. Any tips?
I just use the Debian packages (/me hugs Debian).
AFAIK, all you have to do is compile with the appropriate flags so it
doesn't try to build Oracle support (you need a full Oracle installation),
and also MySQL support.
You also need to have the PostgreSQL loadable Qt 3 module installed before
compiling, plus Qt 3 headers and such.
You could try downloading the binary Debian packages from
packages.debian.org ("unstable" distribution) and unpacking them (Debian
packages are just "ar" packages with extra headers).
-Roberto
--
+| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+
Roberto Mello - Computer Science, USU - http://www.brasileiro.net/
http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer
Cannot open CATFOOD.CAN - Eat logitech mouse instead (Y/n)?
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Scan SQL
On Tue, Jul 23, 2002 at 10:11:18AM -0300, Sandro Joel Eller wrote: > Hi > > I need to make a parser in a sql to get tables and fields to verify the > privileges. Do anybody know software, function or anything that can help me? > > I found software to create a compiler and generate the source in delphi, but I > need a sql specification (BNF, EBNF) and I do not know where to find it. You could look at how PostgreSQL does its parsing. The source code is open. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer I to¤d yo¤, "Never¤touch ¤he flo¤py di¤k su¤face!" ---(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] RES: [GENERAL] set DateStyle to 'SQL'
On Fri, Jul 26, 2002 at 01:56:16PM -0300, Elielson Fontanezi wrote: > The environment variables PGHOST and PGPORT specify to client applications > the host and port of the database server, overriding the compiled-in > defaults. If you are going to run client applications remotely then it is > convenient if every user that plans to use the database sets PGHOST. This is > not required, however: the settings can be communicated via command line > options to most client programs. > --- > > But... likely there are much more variables rather than PGDATA, > PGHOST and PGPORT. > Where can I find them? Perhaps you're looking for http://www.postgresql.org/idocs/index.php?runtime-config.html Please trim your replies to the list. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer A .45 beats a royal flush EVERY TIME ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: RES: [SQL] RES: [GENERAL] set DateStyle to 'SQL'
On Mon, Jul 29, 2002 at 08:57:12AM -0300, Elielson Fontanezi wrote: > Bom dia Roberto! Bom Dia! :-) > I have just read these documents. > The variables listed in the link below, say about variable that I > cannot > set from a .profile file. > By the way, if I wat to state that all NUMERIC values must follow a > format > as ###,###.##& these docs do not say how to do that. > I think this makes me clear for what I am look for. I don't know either, although I think this has been answered in the postgresql list in portuguese at http://br.groups.yahoo.com/group/postgresql-br/ The archives are searchable, so you could try there. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Advisor: The guy who told you how to screw up ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] License
On Thu, Aug 01, 2002 at 02:46:47PM +0800, Christopher Kings-Lynne wrote: > Postgres is totally, absolutely free. It comes under the terms of the BSD > license which means you have the right to use and modify the source code in > any way you wish, so long as you acknowledge that the code originated in > Postgres. It is even more free than MySQL. It should be mentioned that PostgreSQL has great community support. If you require commercial support, that is provided by several companies, including Pgsql Inc., Red Hat, Aglio, etc. -Roberto -- +| http://fslc.usu.edu/ USU Free Software & GNU/Linux Club |--+ Roberto Mello - Computer Science, USU - http://www.brasileiro.net/ http://www.sdl.usu.edu/ - Space Dynamics Lab, Developer Itsdifficulttobeverycreativewithonlyfiftysevencharacters! ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] [GENERAL] Monitoring a Query
On Sun, Sep 22, 2002 at 09:51:55PM -0400, Bruce Momjian wrote: > > > > Would it be a good idea to add the time that the current query began > > execution at to pg_stat_activity? > > What do people think about this? It seems like a good idea to me. OpenACS has a package called "Developer Support" that shows you (among other things) how long a query took to be executed. Very good to finding out slow-running queries that need to be optimized. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] [GENERAL] Monitoring a Query
On Mon, Sep 23, 2002 at 10:48:30AM -0400, Bruce Momjian wrote: > > > > > > > > Would it be a good idea to add the time that the current query began > > > > execution at to pg_stat_activity? > > > > > > What do people think about this? It seems like a good idea to me. > > > > OpenACS has a package called "Developer Support" that shows you (among > > other things) how long a query took to be executed. Very good to finding > > out slow-running queries that need to be optimized. > > 7.3 will have GUC 'log_duration' which will show query duration. Forgive my ignorance here, but what is GUC? And how would I access the query duration? -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: What is purple and commutes? A: A boolean grape. ---(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
Re: PROBLEM SOLVED RE: [SQL] please help with converting a view in oracle into postgresql readably code
On Tue, Oct 01, 2002 at 11:55:14PM -0700, mgeddert wrote: > Robert, > > Thanks for the help, I kept on playing with what you gave me, and after > removing one () pair and adding the ENDs to the CASE WHENs it works! > Thank you so much for the help, I have been very frustrated with this > for a number of days now. Argh, that's right, I forgot the ENDs. Ugh! Shame on me. Glad that's working for you now. -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: Heard about the who couldn't spell? A: He spent the night in a warehouse. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] epoch to date
Hi all, Maybe I missed something obvious, but is there a built-in function to convert epoch to date? I couldn't find it in the documentation for extract and friends. Thanks, -Roberto -- +| Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + All in all, you're just another brick on the wall... ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
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
Re: [SQL] SELECT with INNER data bases
On Thu, Oct 10, 2002 at 09:37:49AM -0300, 2000 Informática wrote: > Hi, > > I have two data bases: db1 and db2. > In MS SQL Server I can use > 'SELECT T1.ID, T2.NAME FROM db1..table1 T1 INNER JOIN db2..table2 T2 > ON T1.ID = T2.ID ...'. > > How I do it in the PosgreSQL ? > > HELP ME, PLEASE ! > > Sorry, > I'm a Brazilian and I don't speak and write English very well ! PostgreSQL doesn't allow this by default. You can use 7.3 and schemas as as suggested, or use a loadable module called dblink with 7.2. dblink is under the PostgreSQL contrib/ directory. See its documentation. And here goes the same as above, but in Portuguese :) O PostgreSQL não permite isso por padrão. Você pode usar 7.3 e schemas como foi sugerido, ou usar um módulo chamado dblink com 7.2. Esse módulo está no diretório contrib do PostgreSQL. Veja sua documentação. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + But what can you do with it? -- ubiquitous cry from Linux-user partner ---(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] Select table from other database
On Mon, Oct 21, 2002 at 11:54:53AM +0100, Tiago Moitinho wrote: > Hi, > > I would like to know if this is possible: > > I have a database A, with a table X. > Is it possible, being logged in another database (B, for instance), to make > queries using table X from database A? > (something like "SELECT * FROM A.X ...") See the dblink module in the PostgreSQL contrib tree (available via ftp. packages available). -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Sigh. I like to think it's just the Linux people who want to be on the "leading edge" so bad they walk right off the precipice. -- Craig E. Groeschel ---(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] Foreign character struggles
On Fri, Oct 25, 2002 at 10:37:59AM -0400, Tom Lane wrote: > > I think this is a locale issue, not a character set issue. You > definitely need --enable-locale, but I doubt you need either of the > above (unless you need to deal with Unicode or Far-Eastern languages). Where is the procedure for working with i18n'd characters described in the documentation? I'm looking for something that mentions the specifics of locale interaction and all that. I ask because the sort of question Tim asked is a recurrent one in a portuguese PostgreSQL mailing list I subscribe to. Thanks, -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + ---(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] functions that return a dataset or set of rows
On Thu, Oct 17, 2002 at 05:41:22PM -0400, Brian wrote: > Is it not possible in 7.2? In 7.2 you can return a cursor, which gets close and lets you basically accomplish the goal. See the PL/pgSQL developer documentation for 7.3 (returning cursors was omitted accidentally (?) in the 7.2 documentation). http://developer.postgresql.org/ -Roberto -- +| Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Bill Gates made $6.3 Billion selling us MS-DOS? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] multiple databases
On Sat, Oct 26, 2002 at 03:15:18PM +1000, peter wrote: > I need to create a recordset in vb which combines a view/table from 2 > different databases. Is this possible? in mssql you could go > dbo.databasename.tablename. Can it be done in vb code? There's an extension to PostgreSQL, called dblink, that allows it. Look at the contrib tree on the PostgreSQL archives. -Roberto -- +| Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + "Data! I thought you were dead!" "No, Sir. I rebooted." ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] CoreReader
On Fri, Oct 25, 2002 at 10:32:00PM -0500, John Ragan wrote: > > Does anybody know of CoreReader being run against > PostGresql? > > If so, I would appreciate knowing the results. What's CoreReader? -Roberto -- +----| Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Time difference without intervals
I saw this question on another PG mailing list, and I don't know how to answer. I've tried casting the interval to a timestamp, but that doesn't seem to be supported in 7.2. Does anyone have an answer? Is there any way to make a timestamp difference operation not return an interval? I'd like to get hours, minutes and seconds only, not the "1 day" or whatnot. lbn-dev=# select now() - '2002-11-07 7:42'::timestamp; ?column? --- 1 day 02:01:57.987838 (1 row) Thanks, -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + TAGLINE NO ESPELHO ¤ OHLEPSE ON ENILGAT ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Time difference without intervals
On Fri, Nov 08, 2002 at 04:11:05PM -0500, Tom Lane wrote: > Roberto Mello <[EMAIL PROTECTED]> writes: > > Is there any way to make a timestamp difference operation not return an > > interval? I'd like to get hours, minutes and seconds only, not the "1 day" > > or whatnot. > > I think your complaint is not that you get an interval, but that you > want it displayed differently. See to_char() ... It was not a complaint in any way. I apologize if I came across like that. It was an honest question. Thanks for the replies. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + "Apple" (c) Copyright 1767, Sir Isaac Newton. ---(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] changing the size of a column without dump/restore
On Mon, Nov 25, 2002 at 01:57:28PM -0500, Michael Richards wrote: > I've got a huge database table and I need to increase the size of a > varchar from like 100 to 200 characters. As I recall the size is just > a restriction and doesn't actually affect the format of the table > file. > > Rather than dumping/restoring a 5Gb table with 20,000,000 rows which > will take all day and night, is there anything I can twiddle in the > system tables to change this size? I'd of course be backing up the > data just in case! PG doesn't have an 'alter table' to increase the column size of a varchar. But you can accomplish it by manipulating the system tables directly. The size of a varchar is stored in pg_attribute as the actual size + 4. For example to change a column "foo" in table "bar" to 200: update pg_attribute set atttypmod = 204 where attrelid = ( select oid from pg_class where relname = 'bar' ) and attname = 'foo'; -Roberto P.S: I don't know if this has any bad side effects. -Roberto -- +|Roberto Mello -http://www.brasileiro.net/ |--+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + * JHM wonders what Joey did to earn "I'd just like to say, for the record, that Joey rules." -- Seen on #Debian ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
