Re: [SQL] yet another simple SQL question
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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] yet another simple SQL question
"A. Kretschmer" <[EMAIL PROTECTED]> 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.
Re: [SQL] yet another simple SQL question
Στις Τρίτη 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Transactions and Exceptions
Richard Huxton <[EMAIL PROTECTED]> 2007-06-22 19:00 >>> >Bart Degryse wrote: >>> 2. Using dblink / dbi-link to reconnect to the database, which means >>> your logging will take place in its own transaction. >> This I like more. Though I don't use either dblink nor dbi-link, I do use >> this kind of >> 'double' connections already for other purposes in which transactions were >> never an >> issue. So it never crossed my mind that I could use them for this too. >> For the time being that's the way I'll walk. Thanks a lot! >> >> Reading your suggestions I assume PostgreSQL lacks something like Oracle's >> PRAGMA AUTONOMOUS_TRANSACTION > >It might well be a useful feature (particularly for logging-style >interactions as you have). I'm not sure it's particularly simple to do >(other than by having a whole new backend as dblink will give you). > >> Shouldn't it be added, are there any plans in that direction? Returning a >> status code >> is not always an option and using some dbi variant certainly isn't because >> of the >> need for perlu. So then you're stuck?! > >Well, dblink is pure C iirc. I think the advantage with dbi-link is that >you can connect to any type of database DBI can. > >-- > Richard Huxton > Archonet Ltd You're right of course about dblink. We never installed it though because it lacks the ability to connect to non-postgresql databases. dbi-link on the other hand didn't make it either because of the overhead. It basically duplicates all tables in the source database, which is too much if you only need say 8 tables of the 300 that exist. So we have chosen to make our own 'interface' having just what we need and nothing more. Using it to have our logging system in a seperate transaction is perfectly possible, we just didn't think of it ourselfs. Nevertheless I think an equivalent to PRAGMA AUTONOMOUS_TRANSACTION would be nice to have. Thanks for your help and insight Richard.
[SQL] Where clause
Hello, I have a table called tracking, with a contactid varchar, click bool, view bool and cid varchar. I would like to put the following into one single query if possible: // Number of clicks select cid,count(distinct contactid) from tracking where click = true group by cid; // Number of views select cid,count(distinct contactid) from tracking where view = true group by cid; I guess I have to put where (click = true or view = true) - and differentiate them in the SELECT target.. ? Many thanks for any input, Mike ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Where clause
am Tue, dem 26.06.2007, um 10:24:05 +0200 mailte Michael Landin Hostbaek folgendes: > Hello, > > I have a table called tracking, with a contactid varchar, click bool, > view bool and cid varchar. > > I would like to put the following into one single query if possible: > > // Number of clicks > select cid,count(distinct contactid) from tracking where click = > true group by cid; > > // Number of views > select cid,count(distinct contactid) from tracking where view = > true group by cid; > > I guess I have to put where (click = true or view = true) - and > differentiate them in the SELECT target.. ? *untested* select cid, sum(case when click = true then 1 else 0 end), sum(case when view = true then 1 else 0 end) from ... Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Where clause
A. Kretschmer (andreas.kretschmer) writes: > *untested* > > select cid, sum(case when click = true then 1 else 0 end), sum(case when > view = true then 1 else 0 end) from ... > Thanks, but I need the DISTINCT contactid - I don't want the same contactid counted twice. Mike ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Where clause
Στις Τρίτη 26 Ιούνιος 2007 12:44, ο/η Michael Landin Hostbaek έγραψε: > A. Kretschmer (andreas.kretschmer) writes: > > *untested* > > > > select cid, sum(case when click = true then 1 else 0 end), sum(case when > > view = true then 1 else 0 end) from ... > > Thanks, but I need the DISTINCT contactid - I don't want the same > contactid counted twice. > Something like SELECT distinct cid,(select count(distinct t1.contactid) from tracking t1 where t1.view and t1.cid=tglob.cid) as countviews,(select count(distinct t2.contactid) from tracking t2 where t2.click and t2.cid=tglob.cid) as countclicks from tracking tglob; ? > Mike > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend -- Achilleas Mantzios ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Where clause
Michael Landin Hostbaek wrote: A. Kretschmer (andreas.kretschmer) writes: *untested* select cid, sum(case when click = true then 1 else 0 end), sum(case when view = true then 1 else 0 end) from ... Thanks, but I need the DISTINCT contactid - I don't want the same contactid counted twice. ... GROUP BY cid -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] yet another simple SQL question
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] NO DATA FOUND Exception
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 ;
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
Re: [SQL] NO DATA FOUND Exception
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" <[EMAIL PROTECTED]> 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 ;
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
Re: [SQL] NO DATA FOUND Exception
>>"Fernando Hevia" <[EMAIL PROTECTED]> 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!!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Vacation days
On Monday 25 June 2007 15:22, Susan Young wrote: > Hi Wei, > That's OK - Enjoy! > Susan > > Wei Weng wrote: > > Can I take next week off? > > > > Thanks! > > > > Wei hi, susan, a change of plan. :) Instead of the whole week, I just wanted to take next monday and tuesday off. Thanks! Wei ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Vacation days
The same KMail bug that bit someone else just yesterday?? Enjoy the vacations anyway ... Wei Weng wrote: > On Monday 25 June 2007 15:22, Susan Young wrote: > > Hi Wei, > > That's OK - Enjoy! > > Susan > > > > Wei Weng wrote: > > > Can I take next week off? > > > > > > Thanks! > > > > > > Wei > > hi, susan, a change of plan. :) > > Instead of the whole week, I just wanted to take next monday and tuesday off. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: 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] Vacation days
On Tue, 26 Jun 2007 13:04:14 -0400 Wei Weng <[EMAIL PROTECTED]> wrote: > On Monday 25 June 2007 15:22, Susan Young wrote: > > Hi Wei, > > That's OK - Enjoy! > > Susan > > > > Wei Weng wrote: > > > Can I take next week off? > > > > > > Thanks! > > > > > > Wei > > hi, susan, a change of plan. :) > > Instead of the whole week, I just wanted to take next monday and > tuesday off. > > Thanks! > > Wei > Vacation denied - you need to work double shifts the next 50 years :-) (have a nice vacation) JesperKP ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Vacation days
[EMAIL PROTECTED] (Wei Weng) writes:
> On Monday 25 June 2007 15:22, Susan Young wrote:
>> Hi Wei,
>> That's OK - Enjoy!
>> Susan
>>
>> Wei Weng wrote:
>> > Can I take next week off?
>> >
>> > Thanks!
>> >
>> > Wei
>
> hi, susan, a change of plan. :)
>
> Instead of the whole week, I just wanted to take next monday and tuesday off.
>
> Thanks!
[EMAIL PROTECTED]:5882=> delete from calendar where day in ('2007-07-02',
'2007-07-03');
ERROR: permission denied for relation calendar
Oops. Apparently that isn't allowed.
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://cbbrowne.com/info/sap.html
"I think fish is nice, but then I think that rain is wet, so who am I
to judge?" -- Ruler of the Universe, HHGTTG
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[SQL] Delete rules and functions
Hi, I have the following scenerio: a rule on a view which executes a function by means of a select call, which in turn deletes from a table which has on-delete rules on it. When the function is called from the rule, the subsequent delete call in the function doesn't cause the on-delete rules on the table to be taken into account. Is that a bug? Or is the query rewriting not possible from inside functions? ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Where clause
Michael Landin Hostbaek skrev: > Hello, > > I have a table called tracking, with a contactid varchar, click bool, > view bool and cid varchar. > > I would like to put the following into one single query if possible: > > // Number of clicks > select cid,count(distinct contactid) from tracking where click = > true group by cid; > > // Number of views > select cid,count(distinct contactid) from tracking where view = > true group by cid; Untested, not the cleverest formulation, but something like this should work: SELECT * FROM ( select cid,count(distinct contactid) from tracking where click = true group by cid ) c1 FULL OUTER JOIN ( select cid,count(distinct contactid) from tracking where view = true group by cid ) c2 USING (cid); ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] yet another simple SQL question
On 6/27/07, Michael Glaesemann <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Delete rules and functions
Wiebe Cazemier <[EMAIL PROTECTED]> writes: > I have the following scenerio: > a rule on a view which executes a function by means of a select call, which in > turn deletes from a table which has on-delete rules on it. When the function > is called from the rule, the subsequent delete call in the function doesn't > cause the on-delete rules on the table to be taken into account. Please provide an example, because the rewriter is most certainly applied to queries from functions. I suspect you are actually being burnt by some other effect, like a row disappearing from the view as soon as its underlying data is deleted. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] Delete rules and functions
On Tuesday 26 June 2007 22:50, Tom Lane wrote: > Please provide an example, because the rewriter is most certainly > applied to queries from functions. > > I suspect you are actually being burnt by some other effect, like a row > disappearing from the view as soon as its underlying data is deleted. Here is an example (and it's nothing more than an example...): -- CREATE TABLE cars ( id SERIAL PRIMARY KEY ); -- CREATE TABLE car_parts ( id SERIAL PRIMARY KEY, car_id INTEGER NOT NULL REFERENCES cars ON DELETE CASCADE, steering_wheel_id INTEGER NOT NULL REFERENCES steering_wheels ); -- CREATE RULE AS ON DELETE TO car_parts DO ALSO ( DELETE FROM steering_wheels WHERE id = OLD.steering_wheel_id; ); -- CREATE VIEW cars_view AS SELECT * FROM cars; -- CREATE FUNCTION cars_delete(p_old) RETURNS VOID AS $$ BEGIN DELETE FROM cars where id = p_old.id; END; $$ LANGUAGE plpgsql; -- CREATE RULE AS ON DELETE TO cars_view DO INSTEAD ( SELECT cars_delete(); ); -- Now, when I delete a row from the cars_view, the underlying record from car is deleted, which cascades to car_parts. The rule intended for removing the steering wheel doesn't do anything. And now that I wrote it, I can see that it's indeed because OLD no longer exists. I knew this was the case for rules, but I overlooked it, apparently... I had already converted it to use triggers, and I'll leave it that way. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
