yet another simple SQL question

Most of these commands are available within the online documentation:

http://www.postgresql.org/docs/8.2/interactive/functions.html

That said, you might try:

UPDATE mytable SET firstname = TRIM(SUBSTRING(firstname,1,position(' '
in firstname)));

Charley

Joshua wrote:

I would use the same string functions and tools that you used in the
previous solution. Look for whitespace, and then break up the string
based on that character. The function list is located here for your
reference: http://www.postgresql.org/docs/8.2/static/functions-string.html

Joshua wrote:

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 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;

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 IN
SELECT *
FROM <table>
WHERE prefix = v_search
LOOP
return 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.

"A. Kretschmer" <andreas.kretschmer@schollglas.com> 2007-06-25 20:07 >>>

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

While there are several ways to make the split the result will never be good. As someone
responded before: this is multicultural. You can never garantee that the first name stops at the
first space. What about names like Sue Ellen or Pieter Jan. I know people with those names
and none of them would like to be calles Sue or Pieter and right they are. Simply because their
first name doesn't stop at the first space. In many countries the concept of 'middle initials' is
meaningless because no one ever uses it.
In my (humble) opinion there are two roads to walk. Either you get your data from the 'client'
split up to the level of detail you require, if someone knows it's him/her. Or you use the data
as is and you don't split it up.

Στις Τρίτη 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.

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%';

"Fernando Hevia" <fhevia@ip-tel.com.ar> 2007-06-26 16:25 >>>

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.

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"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:-)