Thread: yet another simple SQL question

Ok,

You guys must be getting sick of these newbie questions, but I can't
resist since I am learning a lot from these email lists and getting
results quick! Thanks to everyone for their contributions.

Here is my questions....

I have a column that looks like this

firstname
-----------------
John B
Mark A
Jennifer D

Basically I have the first name followed by a middle initial. Is there a
quick command I can run to strip the middle initial? Basically, I just
need to delete the middle initial so the column would then look like the
following:

firstname
---------------
John
Mark
Jennifer

Thanks again for all of your help today. Everything you guys have been
sending has produced successful results.

Thanks.

On Monday 25 June 2007 12:44:25 Joshua wrote:
> Ok,
>
> You guys must be getting sick of these newbie questions, but I can't
> resist since I am learning a lot from these email lists and getting
> results quick! Thanks to everyone for their contributions.
>
> Here is my questions....
>
> I have a column that looks like this
>
> firstname
> -----------------
> John B
> Mark A
> Jennifer D
>
> Basically I have the first name followed by a middle initial. Is there a
> quick command I can run to strip the middle initial? Basically, I just
> need to delete the middle initial so the column would then look like the
> following:
>
> firstname
> ---------------
> John
> Mark
> Jennifer
>
> Thanks again for all of your help today. Everything you guys have been
> sending has produced successful results.
>
> Thanks.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

Well, the simple way I could think of to do this would to be a simple regex 
(the query mock-up below is untested)...

select regexp_replace(COLUMN, '(.*)\\s\\w$', '\\1', 'g') ...

This doesn't seem like a difficult thing to do in application code.  It seems 
like it makes more sense to do it there.

-- 
~ manchicken <><
(A)bort, (R)etry, (I)nfluence with large hammer.
09 F9 11 02 9D 74 E3 5B D8 41 56 C5 63 56 88 C0


Please avoid sending me Word or PowerPoint attachments.
See http://www.gnu.org/philosophy/no-word-attachments.html

am  Mon, dem 25.06.2007, um 12:44:25 -0500 mailte Joshua folgendes:
> Ok,
>
> You guys must be getting sick of these newbie questions, but I can't
> resist since I am learning a lot from these email lists and getting
> results quick! Thanks to everyone for their contributions.
>
> Here is my questions....
>
> I have a column that looks like this
>
> firstname
> -----------------
> John B
> Mark A
> Jennifer D
>
> Basically I have the first name followed by a middle initial. Is there a
> quick command I can run to strip the middle initial? Basically, I just
> need to delete the middle initial so the column would then look like the
> following:
>
> firstname
> ---------------
> John
> Mark
> Jennifer

Yes, of course:

test=# select split_part('My Name', ' ', 1);
 split_part
------------
 My
(1 row)

And now, i think, you should read our fine manual:
http://www.postgresql.org/docs/current/interactive/


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

On mán, 2007-06-25 at 12:44 -0500, Joshua wrote:
> I have a column that looks like this
> 
> firstname
> -----------------
> John B
> Mark A
> Jennifer D
> 
> Basically I have the first name followed by a middle initial. Is there a 
> quick command I can run to strip the middle initial?

how about:
 select regexp_replace(firstname,' .*','') as firstname  from footable;

or:
 select substring(firstname FROM '(.*) ') as firstname  from footable;


gnari

On Jun 25, 2007, at 12:44 PM, Joshua wrote:

> Ok,
>
> You guys must be getting sick of these newbie questions, but I
> can't resist since I am learning a lot from these email lists and
> getting results quick! Thanks to everyone for their contributions.
>
> Here is my questions....
>
> I have a column that looks like this
>
> firstname
> -----------------
> John B
> Mark A
> Jennifer D
>
> Basically I have the first name followed by a middle initial. Is
> there a quick command I can run to strip the middle initial?
> Basically, I just need to delete the middle initial so the column
> would then look like the following:
>
> firstname
> ---------------
> John
> Mark
> Jennifer
>
> Thanks again for all of your help today. Everything you guys have
> been sending has produced successful results.
>

Try something along the lines of:


SELECT substring(firstname from '^(\w*)\W') from table_name;


Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

Hi.

Im taking my first steps with plpgsql.
I want my function to react to the result of a query in the following way:

begin select column into variable from table where condition;
exception when <<data_found>> then return variable; when <<no_data_found>> then <<do nothing/continue>> ; when
<<any_other_exception>>then <<raise some error message>> ;
 
end ;

Is something like this possible en plpgsql without recurring to a select
count(*) to check how many results I will get?


Actual code is:

CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS
$body$
DECLARE v_len integer DEFAULT 8; v_search varchar; v_register num_geo%ROWTYPE;
BEGIN
 -- Search loop WHILE v_len > 0 LOOP   v_search := substring(p_line, 1, v_len);   begin     SELECT * INTO v_register
WHEREprefix = v_search;   exceptionwhen no_data then        -- Getting error here    continue;       when others then
return v_register.prefix;   end;       v_len := v_len - 1; END LOOP;
 
 raise 'Not found';
END;
$body$
LANGUAGE 'plpgsql' VOLATILE ;


ERROR: unrecognized exception condition "no_data"
SQL state: 42704
Context: compile of PL/pgSQL function "test" near line 14


Thanks,
Fernando.

On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote:
>   when <<data_found>> then return variable;
>   when <<no_data_found>> then <<do nothing/continue>> ;
>   when <<any_other_exception>> then <<raise some error message>> ;

Check out the FOUND variable in the documentation for the first two,
and the "trapping errors" section for the latter.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what 
you told them to.  That actually seems sort of quaint now.    --J.D. Baldwin

>On Mon, Jun 25, 2007 at 04:20:37PM -0300, Fernando Hevia wrote:
>>   when <<data_found>> then return variable;
>>   when <<no_data_found>> then <<do nothing/continue>> ;
>>   when <<any_other_exception>> then <<raise some error message>> ;
>
>Check out the FOUND variable in the documentation for the first two,
>and the "trapping errors" section for the latter.
>
>Andrew Sullivan  | ajs@crankycanuck.ca

Thanks for the tip. I was looking in the wrong place.
The FOUND variable is explained in chapter "37.6.6. Obtaining the Result
Status".

Thanks again,
Fernando.

[Please create a new message to post about a new topic, rather than  
replying to and changing the subject of a previous message. This will  
allow mail clients which understand the References: header to  
properly thread replies.]

On Jun 25, 2007, at 14:20 , Fernando Hevia wrote:

> Is something like this possible en plpgsql without recurring to a  
> select
> count(*) to check how many results I will get?

I think you want to look at FOUND.

http://www.postgresql.org/docs/8.2/interactive/plpgsql- 
statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

For example:

# select * from foos;
foo
-----
bar
baz
bat
(3 rows)

# CREATE FUNCTION foos_exist()
RETURNS boolean
LANGUAGE plpgsql AS $body$
DECLARE        v_foo TEXT;
BEGIN        SELECT INTO v_foo               foo        FROM foos;        IF FOUND THEN RETURN TRUE;        ELSE RETURN
FALSE;       END IF;
 
END;
$body$;
CREATE FUNCTION
# select foos_exist();
foos_exist
------------
t
(1 row)

# truncate foos;
TRUNCATE TABLE
test=# select foos_exist();
foos_exist
------------
f
(1 row)

> Actual code is:
>
> CREATE OR REPLACE FUNCTION test(p_line text) RETURNS text AS
> $body$
> DECLARE
>   v_len integer DEFAULT 8;
>   v_search varchar;
>   v_register num_geo%ROWTYPE;
> BEGIN
>
>   -- Search loop
>   WHILE v_len > 0 LOOP
>     v_search := substring(p_line, 1, v_len);
>     begin
>       SELECT * INTO v_register WHERE prefix = v_search;
>     exception
>     when no_data then        -- Getting error here
>         continue;
>         when others then
>         return v_register.prefix;
>     end;
>     v_len := v_len - 1;
>   END LOOP;

I think you might want to rewrite this using some of the information  
here:

http://www.postgresql.org/docs/8.2/interactive/plpgsql-control- 
structures.html#PLPGSQL-RECORDS-ITERATING

For example, your inner loop could loop could look something like this:

FOR v_register INSELECT *FROM <table>WHERE prefix = v_searchLOOPreturn v_register.prefix;
END LOOP;

If no data is found, the loop won't do anything.

However, it looks like you're trying to return a set of results  
(i.e., many rows), rather than just a single row. You'll want to look  
at set returning functions. One approach (probably not the best)  
would be to expand p_line into all of the possible v_search items and  
append that to your query, which would look something like:

SELECT prefix
FROM
<table>
WHERE prefix IN (<list of v_search items>).

Another way to do this might be to not use a function at all, but a  
query along the lines of

SELECT prefix
FROM <table>
WHERE p_line LIKE prefix || '%';

Hope this helps.

Michael Glaesemann
grzm seespotcode net

Joshua wrote:
> Ok,
>
> You guys must be getting sick of these newbie questions, but I can't 
> resist since I am learning a lot from these email 

I'm not fond of people using meaningless subjects, or of people 
simultaneously posting the same message to other lists. If one chooses a 
meaningless subject, I mostly ignore the question.

Subjects such as yours don't cut the mustard. Try to summarise your 
problem; if I'm interested in the problem then I will read it and 
(maybe) help.

When I find it's cross-posted, I'm likely to change my mind.

-- 
Grumpy.

Στις Τρίτη 26 Ιούνιος 2007 09:40, ο/η John Summerfield έγραψε:
> Joshua wrote:
> > Ok,
> >
> > You guys must be getting sick of these newbie questions, but I can't
> > resist since I am learning a lot from these email
>
> I'm not fond of people using meaningless subjects, or of people
> simultaneously posting the same message to other lists. If one chooses a
> meaningless subject, I mostly ignore the question.

The subject is of clasical unix flavor, since it points back to those
wild YACC years of our youth, so i think most unix grown ups kind of like
subjects like that.

>
> Subjects such as yours don't cut the mustard. Try to summarise your
> problem; if I'm interested in the problem then I will read it and
> (maybe) help.
>
> When I find it's cross-posted, I'm likely to change my mind.

Why do you think that criticizing was better than staying silent?
Anyway, Joshua already took some very enlightening answers enuf to get him
going.

--
Achilleas Mantzios

On Jun 26, 2007, at 2:19 , Achilleas Mantzios wrote:

> Στις Τρίτη 26 Ιούνιος 2007 09:40, ο/η John
> Summerfield έγραψε:
>>
>> Subjects such as yours don't cut the mustard. Try to summarise your
>> problem; if I'm interested in the problem then I will read it and
>> (maybe) help.
>>
>> When I find it's cross-posted, I'm likely to change my mind.
>
> Why do you think that criticizing was better than staying silent?
> Anyway, Joshua already took some very enlightening answers enuf to
> get him
> going.

While self-admittedly grumpy, I believe John was trying to encourage
better posting behavior from Joshua which will benefit him by
receiving more answers. If John had remained silent (as I'm sure
others who share his sentiment have), being (apparently) new, Joshua
probably wouldn't know he's potentially limiting the number of
answers he'd receive. Perhaps John could have phrased his email
differently, but I think he was trying to help Joshua.

Michael Glaesemann
grzm seespotcode net

On Jun 25, 2007, at 17:05, Michael Glaesemann wrote:

>[Please create a new message to post about a new topic, rather than  
>replying to and changing the subject of a previous message. This will  
>allow mail clients which understand the References: header to  
>properly thread replies.]

Wasn't aware of this. Will do.
I should obtain a better mail client.

>However, it looks like you're trying to return a set of results  
>(i.e., many rows), rather than just a single row. You'll want to look  
>at set returning functions. One approach (probably not the best)  
>would be to expand p_line into all of the possible v_search items and  
>append that to your query, which would look something like:

Thank you for your help. All the advice was very useful and I have now a
working function. 
I still have an issue left: I would like my function to return multiple
values (as in columns of a row).
Actually I found two possibilities: array and record. I ended up using
arrays since I couldn't figure out how to access the record data from
outside the function. Nevertheless I think a solution based on returning a
record type when you actually want to return the whole row would be more
elegant.

For example:

CREATE TABLE table1 (  field1 text,  field2 text,  field3 text
);

INSERT INTO table1 ('data1', 'data2', 'data3');

CREATE FUNCTION my_func() RETURNS record AS
$body$
DECLARE v_row table1%ROWTYPE;
BEGIN
 SELECT *  INTO v_row FROM table1 WHERE <condition> ;
 IF FOUND THEN    RETURN v_row; END IF;
 RETURN NULL;

END;
$body$
LANGUAGE 'plpgsql';


SELECT my_func();                 my_func
---------------------------------------------------
(data1, data2, data3)

How do I refer a specific field of the returned row from outside the
function? How should I write the query in order to show only fields 1 and 3,
for example?

It's sad to bother with this syntax questions, but I've had a hard time
finding code examples online.

Regards,
Fernando.

>>"Fernando Hevia" <fhevia@ip-tel.com.ar> 2007-06-26 16:25 >>>
>>How do I refer a specific field of the returned row from outside the
>>function? How should I write the query in order to show only fields 1 and
3, for example?

>In case you would like to use set returning functions...
> 
>if your function will return records with the same structure as an existing
>table
>CREATE FUNCTION my_func() RETURNS SETOF my_table AS ...
> 
>if not you have to define the returning type
>CREATE TYPE func_row AS ("field1" varchar(10), "field2" integer, "field3"
>integer, ...)
>CREATE FUNCTION my_func() RETURNS SETOF func_row AS ...
>
>now you can use your function
>SELECT * FROM my_func();
> 
>or
> 
>SELECT A.field1, A.field2
>FROM my_func() A left join my_func() B on A.field2 = B.field3
>WHERE A.field1 like 'B%';


Exactly what I was looking for.
Thanks!!

On 6/27/07, Michael Glaesemann <grzm@seespotcode.net> wrote:

> While self-admittedly grumpy, I believe John was trying to encourage
> better posting behavior from Joshua which will benefit him by
> receiving more answers. If John had remained silent (as I'm sure
> others who share his sentiment have), being (apparently) new, Joshua
> probably wouldn't know he's potentially limiting the number of
> answers he'd receive. Perhaps John could have phrased his email
> differently, but I think he was trying to help Joshua.
Which makes this a fine opportunity to post the admirable
http://www.catb.org/~esr/faqs/smart-questions.html

:)


-- Cheers,  Andrej
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm

Michael Glaesemann wrote:
> 
> On Jun 26, 2007, at 2:19 , Achilleas Mantzios wrote:
> 
>> ΣÏ?ιÏ? ΀Ï?ίÏ?η 26 Î?οÏ?ΜιοÏ? 2007 09:40, ο/η John 
>> Summerfield έγÏ?αÏ?ε:
>>>
>>> Subjects such as yours don't cut the mustard. Try to summarise your
>>> problem; if I'm interested in the problem then I will read it and
>>> (maybe) help.
>>>
>>> When I find it's cross-posted, I'm likely to change my mind.
>>
>> Why do you think that criticizing was better than staying silent?
>> Anyway, Joshua already took some very enlightening answers enuf to get 
>> him
>> going.
> 
> While self-admittedly grumpy, I believe John was trying to encourage 
> better posting behavior from Joshua which will benefit him by receiving 
> more answers. If John had remained silent (as I'm sure others who share 
> his sentiment have), being (apparently) new, Joshua probably wouldn't 
> know he's potentially limiting the number of answers he'd receive. 
> Perhaps John could have phrased his email differently, but I think he 
> was trying to help Joshua.
> 
> Michael Glaesemann

Thanks Man of Good Sense.


Andrej:
I don't post links; I often find them unhelpful because I can't read 
them at the time I'm reading email.

esr's article contains good advice, but there's room for argument on 
some of his points. Probably, not everyone will agree with me on Which_ 
points:-)

On 7/2/07, John Summerfield <postgres@herakles.homelinux.org> wrote:

> Andrej:
> I don't post links; I often find them unhelpful because I can't read
> them at the time I'm reading email.
>
> esr's article contains good advice, but there's room for argument on
> some of his points. Probably, not everyone will agree with me on Which_
> points:-)
I guess that's quite likely ... after all, there's humans who aren't ESR
reading ;} involved ... I, too, think that a few points might need alteration,
but over all I think it's quite valid; and it definitely offers some food for
thought, and I believe it nudges people into the direction of at least
AWARENESS of netiquette on varied lists.


Cheers,
Andrej


-- 
Please don't top post, and don't use HTML e-Mail :}  Make your quotes concise.

http://www.american.edu/econ/notes/htmlmail.htm