Re: [SQL] split function for pl/pgsql

2002-10-02 Thread Joe Conway
Frederic Logier wrote: > Great ! have you some example for call a pl/perl function from a > pl/pgsql function ? I don't use PL/Perl, but I think you should be able to do: SELECT INTO var my_plperl_func(text_to_split); from within your PL/pgSQL function. > And could i use an int array in pl/pg

Re: [SQL] celko nested set functions

2002-10-02 Thread Josh Berkus
Robert, > I'm wondering if anyone has written code that enables you to move > entities between parents in a nested set model. Specifically something > that can do it without deleting any of the children involved in the > process. I looked in the postgresql cookbook which had adding and > buildin

Re: [SQL] schedule of v7.3

2002-10-02 Thread Bruce Momjian
Jie Liang wrote: > Bruce, > What is the schedule for releasing v7.3 stable? Oct? We think late October or early November. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts

[SQL] celko nested set functions

2002-10-02 Thread Robert Treat
I'm wondering if anyone has written code that enables you to move entities between parents in a nested set model. Specifically something that can do it without deleting any of the children involved in the process. I looked in the postgresql cookbook which had adding and building tree's, but not m

[SQL] schedule of v7.3

2002-10-02 Thread Jie Liang
Bruce, What is the schedule for releasing v7.3 stable? Oct? Thanks. Jie Liang ---(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] Stored Procedures

2002-10-02 Thread Joe Conway
[EMAIL PROTECTED] wrote: > Ok, if this does not apply to versions prior to 7.3beta > then what do I need to do if I am running 7.2.1? When I > try to use the SETOF to retrun a row set, I only get > one column. First, prior to 7.3 there is no SCHEMA support in Postgres. Everything lives in ess

Re: [SQL] indexing on char vs varchar

2002-10-02 Thread Beth Gatewood
Thanks for the excellent description, Josh. And the reminder of the performance list (it's existence somehow slipped from my mind). -Beth > -Original Message- > From: Josh Berkus [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 02, 2002 10:56 AM > To: Beth Gatewood; 'Bruce Momjian'

Re: [SQL] Stored Procedures

2002-10-02 Thread david williams
Stored procedures returning more than one row up through odbc does not work in 7.2.1   To return more than one column you must spec is column in the returns area of the function.   Dave   - Original Message - From: [EMAIL PROTECTED] Sent: Wednesday, October 02, 2002 1:53 PM To: Joe Conway

Re: [SQL] indexing on char vs varchar

2002-10-02 Thread Josh Berkus
Beth, Oh, and you should take this sort of question to the new performance list: [EMAIL PROTECTED] -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

Re: [SQL] Stored Procedures

2002-10-02 Thread bcschnei
Ok, if this does not apply to versions prior to 7.3beta then what do I need to do if I am running 7.2.1? When I try to use the SETOF to retrun a row set, I only get one column. Do I need to update Postgres to get things to work? Ben > david williams wrote: > > Also, > > > > the table defini

Re: [SQL] indexing on char vs varchar

2002-10-02 Thread Josh Berkus
Beth, > SorryI don't understand. The length is at the front of what? In some RDBMSs, the VARCHAR data type has a 2 or 4-byte indicator of the length of the stored string before the data itself, while CHAR does not require this information because it is fixed-length. This makes the CHAR

Re: [SQL] Updating from select

2002-10-02 Thread Manfred Koizar
On Wed, 02 Oct 2002 19:11:19 +0200, Thrasher <[EMAIL PROTECTED]> wrote: >UPDATE trans_log t SET t.cost = > (SELECT SUM(p.cost) FROM products_log p WHERE p.trans = t.id) Thrasher, try it without the table alias t: UPDATE trans_log SET cost = (SELECT SUM(p.cost) FROM products_log p WHERE

Re: [SQL] indexing on char vs varchar

2002-10-02 Thread Beth Gatewood
SorryI don't understand. The length is at the front of what? -Beth > -Original Message- > From: Bruce Momjian [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, October 02, 2002 10:06 AM > To: Beth Gatewood > Cc: [EMAIL PROTECTED] > Subject: Re: [SQL] indexing on char vs varchar > > > >

[SQL] Updating from select

2002-10-02 Thread Thrasher
Hi all I have two related tables: CREATE TABLE trans_log ( id serial, datetimestamp not null, costnumeric(12,5) NULL ); CREATE TABLE products_log ( id serial, trans integer not null references trans_log(id), costnumeric(12,

Re: [SQL] indexing on char vs varchar

2002-10-02 Thread Bruce Momjian
We store all the text/char/varchar types with the length at the front so we don't have such optimizations. We do have "char", in quotes, which is a single character, but that's about it. --- Beth Gatewood wrote: > Hi- > >

Re: [SQL] split function for pl/pgsql

2002-10-02 Thread Frederic Logier
Le mer 02/10/2002 à 17:44, Joe Conway a écrit : > There is no split function built in to PostgreSQL currently. You could write > it yourself in PL/Perl and use it in the PL/pgSQL function. Great ! have you some example for call a pl/perl function from a pl/pgsql function ? And could i use an int

[SQL] indexing on char vs varchar

2002-10-02 Thread Beth Gatewood
Hi- This is more just trying to understand what is going on under the hood of pgsql. I have read through the archives that there is no difference between index on char, varchar or text. I am wondering why? I understand all the arguments about saving space but I am specifically asking about ind

Re: [SQL] Stored Procedures

2002-10-02 Thread Joe Conway
david williams wrote: > Also, > > the table definition MUST be in the Public Schema. I use my own schema > names but in order for the table to be found by the function it ( the > table ) must be in the public schema. Although it can be empty. (Note: this discussion does not apply to Postgre

Re: [SQL] split function for pl/pgsql

2002-10-02 Thread Joe Conway
Frederic Logier wrote: > hi, > > i'm looking for a split function, like perl or php. > I need doing a pl/pgsql function with a split return an array. > I must do some loop with this array for mass inserting. > > I think of doing it with pl / perl but I need to do insert and I don't > find exampl

Re: [SQL] Stored Procedures

2002-10-02 Thread david williams
Also,   the table definition MUST be in the Public Schema. I use my own schema names but in order for the table to be found by the function it ( the table ) must be in the public schema. Although it can be empty.   DaveGet more from the Web. FREE MSN Explorer download : http://explorer.msn.com

Re: [SQL] Stored Procedures

2002-10-02 Thread david williams
http://developer.postgresql.org/docs/postgres/xfunc-sql.html#AEN30400   See section   9.2.4. SQL Table Functions   - Original Message - From: [EMAIL PROTECTED] Sent: Tuesday, October 01, 2002 4:25 PM To: [EMAIL PROTECTED] Subject: [SQL] Stored Procedures  Hi all. I'm looking for a little

Re: PROBLEM SOLVED RE: [SQL] please help with converting a view in oracle into postgresql readably code

2002-10-02 Thread Roberto Mello
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 nu

[SQL] split function for pl/pgsql

2002-10-02 Thread Frederic Logier
hi, i'm looking for a split function, like perl or php. I need doing a pl/pgsql function with a split return an array. I must do some loop with this array for mass inserting. I think of doing it with pl / perl but I need to do insert and I don't find example with pl / perl and sql. Here my func