Re: [SQL] Correct Insert SQL syntax?
You might wanna check out the PostgreSQL manual. http://www.postgresql.org/docs/ There's definitely an answer in it to all your questions. Especially chapter 38 on migrating from Oracle to PostgreSQL might be helpful. http://www.postgresql.org/docs/8.3/interactive/plpgsql-porting.html >>> "Ruben Gouveia" <[EMAIL PROTECTED]> 2008-09-05 0:56 >>> Thanks Yuri! On Thu, Sep 4, 2008 at 3:49 PM, Yura Gal <[EMAIL PROTECTED]> wrote: There is no internal dual table in PG unlike Ora:) If you need to invoke non-set-returning function simply execute: SELECT my_func(p1, p2...); -- Best regards, Yuri.
Re: [SQL] Correct Insert SQL syntax?
--- On Thu, 9/4/08, Ruben Gouveia <[EMAIL PROTECTED]> wrote: > From: Ruben Gouveia <[EMAIL PROTECTED]> > Subject: [SQL] Correct Insert SQL syntax? > To: "pgsql-sql" > Date: Thursday, September 4, 2008, 10:16 PM > Will this syntax work: > > fcn_stats are all in the same schema > > CREATE OR REPLACE FUNCTION insert_stats(p_date date) > RETURNS void AS $$ > > BEGIN > insert into stats ( > date, > stats1, > stats2 > ) (select > p_date, > > fcn_stats1(p_date,'basic'), > > fcn_stats2(p_date,'basic',0) > from dual > ); > END; > $$ LANGUAGE 'plpgsql'; Dual is a table create for you or is the generic table of oracle? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Correct Insert SQL syntax?
On Fri, Sep 5, 2008 at 10:11 AM, Lennin Caro <[EMAIL PROTECTED]> wrote: > --- On Thu, 9/4/08, Ruben Gouveia <[EMAIL PROTECTED]> wrote: >> ) (select >> p_date, >> >> fcn_stats1(p_date,'basic'), >> >> fcn_stats2(p_date,'basic',0) >> from dual > > Dual is a table create for you or is the generic table of oracle? Dual is a special table oracle creates that always has one row and one row only so you have a target for your from clause always. PostgreSQL has the syntactic weirdness that everything is a function that makes some sql syntax hard to implement or get changed, oracle's weirdness (well, one of many really) is the requirement of a target table. the spec would seem to side with oracle on this, but it is a pain the butt. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] variables with SELECT statement
Hello All: I have a simple issue. Within my table there is a field DESCRIPTION that I would like to parse and split out into other fields. Within DESCRIPTION there are spaces that separate the data items. String_to_array(description, ' ') does the job very well. I need something like this to work. select string_to_array(description, ' ') as a_desc, a_desc[0] as name , a_desc[1] as type, a_desc[2] as size, from prodlist where type = 'B' Need to parse the DESCRIPTION and then reference the pieces. Your kind assistance is requested. Thanks Kevin Duffy WR Capital Management 40 Signal Rd Stamford, CT 203-504-6221
Re: [SQL] variables with SELECT statement
Kevin Duffy wrote: Within my table there is a field DESCRIPTION that I would like to parse and split out into other fields. Within DESCRIPTION there are spaces that separate the data items. String_to_array(description, ‘ ‘) does the job very well. I need something like this to work. select string_to_array(description, ' ') as a_desc, a_desc[0] as name , a_desc[1] as type, a_desc[2] as size, from prodlist where type = 'B' You almost had it ... select a_desc, a_desc[1] as name, a_desc[2] as type, a_desc[3] as size from (select string_to_array(description, ' ') as a_desc from prodlist) as foo where a_desc[2] = 'B' -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
OK that is a syntax I have never seen. But correct we are getting close. Noticed that string_to_array does not handle double spaces very well. If there are double space between the tokens, there is "" (empty string) in the array returned. Not exactly what I expected. KD -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax Sent: Friday, September 05, 2008 4:07 PM Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] variables with SELECT statement Kevin Duffy wrote: > Within my table there is a field DESCRIPTION that I would like to parse > and split out into other fields. > > Within DESCRIPTION there are spaces that separate the data items. > String_to_array(description, ' ') does the job very well. > > I need something like this to work. > > select string_to_array(description, ' ') as a_desc, > a_desc[0] as name , a_desc[1] as type, a_desc[2] as > size, from prodlist where type = 'B' You almost had it ... select a_desc, a_desc[1] as name, a_desc[2] as type, a_desc[3] as size from (select string_to_array(description, ' ') as a_desc from prodlist) as foo where a_desc[2] = 'B' -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Kevin Duffy wrote: Noticed that string_to_array does not handle double spaces very well. If there are double space between the tokens, there is "" (empty string) in the array returned. Not exactly what I expected. Try regexp_replace http://www.postgresql.org/docs/8.3/interactive/functions-string.html -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Just testing the regexp_string_to_array
This SQL
select description, regexp_string_to_array(description::text , E'\\s+' )
as optdesc, securitytype
from xx where type = 'B' order by 1
produced this error:
ERROR: function regexp_string_to_array(text, text) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You may
need to add explicit type casts.
Character: 21
Don't see the difference between the above and the example in the doc's.
kd
-Original Message-
From: Osvaldo Kussama [mailto:[EMAIL PROTECTED]
Sent: Friday, September 05, 2008 4:47 PM
To: Kevin Duffy
Subject: Re: [SQL] variables with SELECT statement
2008/9/5, Kevin Duffy <[EMAIL PROTECTED]>:
> OK that is a syntax I have never seen. But correct we are getting
> close.
>
> Noticed that string_to_array does not handle double spaces very well.
> If there are double space between the tokens, there is "" (empty
string)
>
> in the array returned. Not exactly what I expected.
>
Try regexp_split_to_array().
http://www.postgresql.org/docs/current/interactive/functions-matching.ht
ml#FUNCTIONS-POSIX-REGEXP
SELECT regexp_split_to_array('the quick brownfox jumped over
the lazy dog', E'\\s+');
regexp_split_to_array
{the,quick,brown,fox,jumped,over,the,lazy,dog}
Osvaldo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Kevin Duffy wrote: Just testing the regexp_string_to_array This SQL select description, regexp_string_to_array(description::text , E'\\s+' ) as optdesc, securitytype from xx where type = 'B' order by 1 produced this error: ERROR: function regexp_string_to_array(text, text) does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You may need to add explicit type casts. Character: 21 Are you running 8.3? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Frank Bax <[EMAIL PROTECTED]> writes: > Kevin Duffy wrote: >> ERROR: function regexp_string_to_array(text, text) does not exist > Are you running 8.3? Also, it's regexp_split_to_array ... regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
No looks like I have 8.2 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Frank Bax Sent: Friday, September 05, 2008 5:13 PM Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] variables with SELECT statement Kevin Duffy wrote: > Just testing the regexp_string_to_array > > This SQL > select description, regexp_string_to_array(description::text , E'\\s+' ) > as optdesc, securitytype > from xx where type = 'B' order by 1 > > produced this error: > > ERROR: function regexp_string_to_array(text, text) does not exist > SQL state: 42883 > Hint: No function matches the given name and argument types. You may > need to add explicit type casts. > Character: 21 Are you running 8.3? -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Thanks Mr. Lane for catching that.
If I run
SELECT regexp_split_to_array('the quick brown fox jumped over the lazy
dog', \\s+');
Straight out of the documentation I get
ERROR: function regexp_split_to_array("unknown", "unknown") does not
exist
Let me guess I have to upgrade.
kd
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
Sent: Friday, September 05, 2008 5:27 PM
To: Frank Bax
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] variables with SELECT statement
Frank Bax <[EMAIL PROTECTED]> writes:
> Kevin Duffy wrote:
>> ERROR: function regexp_string_to_array(text, text) does not exist
> Are you running 8.3?
Also, it's regexp_split_to_array ...
regards, tom lane
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
On Fri, Sep 5, 2008 at 3:28 PM, Kevin Duffy <[EMAIL PROTECTED]> wrote: > No looks like I have 8.2 I can attest that all of 8.3's performance improvements as well all of the really useful new functions like the one mentioned here make it well worth the effort to upgrade. I haven't been as excited about a pgsql version since vacuum (regular) was invented. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
When was 8.3 released? But for today I could do string_to_array(regexp_replace(description, E'\\s+', ' '), ' ') as desc and get what I need to survive. Many thanks for all the replys. Would not have made progress on this by myself. kd -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Friday, September 05, 2008 5:35 PM To: Kevin Duffy Cc: pgsql-sql@postgresql.org; Frank Bax Subject: Re: [SQL] variables with SELECT statement On Fri, Sep 5, 2008 at 3:28 PM, Kevin Duffy <[EMAIL PROTECTED]> wrote: > No looks like I have 8.2 I can attest that all of 8.3's performance improvements as well all of the really useful new functions like the one mentioned here make it well worth the effort to upgrade. I haven't been as excited about a pgsql version since vacuum (regular) was invented. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] variables with SELECT statement
Kevin Duffy wrote: No looks like I have 8.2 This works on 8.2: String_to_array(regexp_replace(description,E'\\s+',' ','g'),' ') -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
