[SQL] query time
Hi I am Esteban from Chile! I need to know.. How can I get running query time ? I mean . is there any function? I hope the answer in english or spanish? thanks! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] query time
Esteban Gutierrez Abarzua writes: > I need to know.. How can I get running query time ? time psql -c 'query here;' -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] group by weirdness
Josh Berkus wrote: > Joseph, > > The subject line could describe a lot of what I see outside my house > every day (I live in San Francisco CA). > > >>Could someome explain these error messages to me? Why am I being >>asked to group by j.id? >> > > Because you've asked the db engine to count on mj.mid. The parser want > you to be specific about whether the other columns are being aggregated > or not. But they are in seperate tables, so how could it think they could be aggregated together? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] group by weirdness
Josh Berkus wrote: > Try putting your sub-selects in the FROM clause instead. (Personally, > I've never found a use for sub-selects in the SELECT clause) > > SELECT j.id, j.created, count(mj.mid), ma1.mcount, ma2.mcount > FROM j, mj, > (SELECTjid, COUNT(oid) as mcount FROM ml > WHERE ml.state <> 11 GROUP BY jid) ma1, > (SELECT jid, COUNT(oid) as mcount FROM ml > WHERE ml.state in (2,5) GROUP BY jid) ma2 > WHERE j.fkey = 1 AND mj.jid = j.id > AND ma1.jid = j.id AND ma2.jid = j.id > GROUP BY j.id, j.created, ma1.mcount, ma2.mcount; > OK that worked for this simple example, but on my real database the performance was horrible, and it didn't work for then there were zero entries in ml (this bites me sometimes, when the AND clause keeps things from working as I think they should). Putting the selects in the SELECT solved both problems. I took out the 'AND ml.jid = j.id' from the outer WHERE (would have also excluded cases where there were zero entries in ml) and only refrenced ml in the subselect. Thanks for your help. -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] trigger trouble -- procedure not found
I'm having trouble creating a trigger. First i'm creating a function, add_to_search in PL/pgSQL. \df verifies the existance of this function: smallint | add_to_search | text, text, text, integer but when I call: CREATE TRIGGER item_insert_search_add AFTER INSERT ON item FOR EACH ROW EXECUTE PROCEDURE add_to_search (name, description, reason, node_id); I get: ERROR: CreateTrigger: function add_to_search() does not exist What am I missing here? It seems to me that I'm missing something very simple, but I can't figure out what it is for the life of me. Kevin Way ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] trigger trouble -- procedure not found
On Sep 14, Kevin Way wrote: > I'm having trouble creating a trigger. First i'm creating a function, > add_to_search in PL/pgSQL. > > \df verifies the existance of this function: > smallint | add_to_search | text, text, text, integer > -- We can verify all the functions which wont return 'OPAQUE'. > but when I call: > > CREATE TRIGGER item_insert_search_add AFTER INSERT > ON item FOR EACH ROW > EXECUTE PROCEDURE add_to_search (name, description, reason, node_id); > > I get: > ERROR: CreateTrigger: function add_to_search() does not exist The function executed by a trigger should by default return a record. ie.. the return type should be 'OPAQUE'. Try the above.. Hope this will work. > > What am I missing here? It seems to me that I'm missing something very > simple, but I can't figure out what it is for the life of me. > > Kevin Way > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(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] trigger trouble -- procedure not found
On Fri, 14 Sep 2001, Kevin Way wrote: > I'm having trouble creating a trigger. First i'm creating a function, > add_to_search in PL/pgSQL. > > \df verifies the existance of this function: > smallint | add_to_search | text, text, text, integer > > but when I call: > > CREATE TRIGGER item_insert_search_add AFTER INSERT > ON item FOR EACH ROW > EXECUTE PROCEDURE add_to_search (name, description, reason, node_id); > > I get: > ERROR: CreateTrigger: function add_to_search() does not exist > > What am I missing here? It seems to me that I'm missing something very > simple, but I can't figure out what it is for the life of me. Trigger functions return opaque and take no arguments. Arguments given on the create trigger statement are passed in a special fashion. (I believe for plpgsql you'd look at TG_NARGS and TG_ARGV[]) ---(end of broadcast)--- TIP 3: 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] trigger trouble -- procedure not found
Thank you. Later checking showed that these requirements were listed in the first sentence of the relevant page. Everything works like a champ now. I've made a small donation to the EFF and to the Red Cross as a minor thanks for your prompt help. Kevin Way ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
