[SQL] btree and is null in a static expression
Hi,
I've written a function but I don't understand the the plan, the planner
makes.
If variables are replaced, the function looks like that:
select a,b,c from "Tbl1" where (a='454') or ('454' is null);
a has got an btree-Index.
explain verbose tells me that Postgres wants to do a SEQSCAN
If the last subexpression is omitted like
select a,b,c from "Tbl1" where (a='454') or false;
The planner wants expecedly utilize an index.
How comes that, while it is obvious that the last subexpression evaluates to
false and thus can be omitted or can even be statically evaluated
I read about btree-Indexes and that they can't be used when null comes into
play. But there?
Thanks
---(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
[SQL] user connection over tcp
hello! i have some problems to understand the manual with pg_hba.conf. i use a tcp connection with phppgadmin to connect to the db. in my pg_hba.conf i say for this connection trust! in phppgadmin i need the for my user the right password to connect. but the manual says that every password will accepted. why isnt that work at me? i also try password in pg_hba.conf for my tcpconnections. but after this i cant connect to the db. but the manual says that users with right password can connect. but this doesnt work here. can somebody explain me whats wrong with me or my postgre?! thx frank habermann ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] user connection over tcp
Did you start the postmaster with -i to allow tcp connections? What is the error that you get when you try to connect? Sean - Original Message - From: "Frank Habermann" <[EMAIL PROTECTED]> To: Sent: Monday, April 18, 2005 9:31 AM Subject: [SQL] user connection over tcp hello! i have some problems to understand the manual with pg_hba.conf. i use a tcp connection with phppgadmin to connect to the db. in my pg_hba.conf i say for this connection trust! in phppgadmin i need the for my user the right password to connect. but the manual says that every password will accepted. why isnt that work at me? i also try password in pg_hba.conf for my tcpconnections. but after this i cant connect to the db. but the manual says that users with right password can connect. but this doesnt work here. can somebody explain me whats wrong with me or my postgre?! thx frank habermann ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] user connection over tcp
Edit "postgres.conf" and "pg_hba.conf" to access database from a remote machine Edit "postgres.conf": -- listen_addresse='*' Edit "pg_hba.conf": -- hostall all 10.1.11.54 255.255.255.0 trust Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Sean Davis Sent: Monday, April 18, 2005 7:13 PM To: Frank Habermann; pgsql-sql@postgresql.org Subject: Re: [SQL] user connection over tcp Did you start the postmaster with -i to allow tcp connections? What is the error that you get when you try to connect? Sean - Original Message - From: "Frank Habermann" <[EMAIL PROTECTED]> To: Sent: Monday, April 18, 2005 9:31 AM Subject: [SQL] user connection over tcp > hello! > > i have some problems to understand the manual with pg_hba.conf. > > i use a tcp connection with phppgadmin to connect to the db. in my > pg_hba.conf i say for this connection trust! in phppgadmin i need the for > my user the right password to connect. but the manual says that every > password will accepted. why isnt that work at me? > > i also try password in pg_hba.conf for my tcpconnections. but after this i > cant connect to the db. but the manual says that users with right password > can connect. but this doesnt work here. > > can somebody explain me whats wrong with me or my postgre?! > > thx > > frank habermann > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > ---(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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] user connection over tcp
hello again. ok. i can connect over tcp!!! this works! my problem is that i only can connect with trust! if i use password i cant connect. with trustmode i need the right password for the user! frank -- Original-Nachricht -- Von: "Frank Habermann" <[EMAIL PROTECTED]> An: "pgsql-sql@postgresql.org" Datum: Montag 18 April 2005 15:31:10 Betreff: user connection over tcp > hello! > > i have some problems to understand the manual with pg_hba.conf. > > i use a tcp connection with phppgadmin to connect to the db. in my > pg_hba.conf i say for this connection trust! in phppgadmin i need the for my > user the right password to connect. but the manual says that every password > will accepted. why isnt that work at me? > > i also try password in pg_hba.conf for my tcpconnections. but after this i > cant connect to the db. but the manual says that users with right password > can connect. but this doesnt work here. > > can somebody explain me whats wrong with me or my postgre?! > > thx > > frank habermann > ---(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
[SQL] How to add 1 hour in a date or time stamp?
How to add 1 hour in a date or time stamp? Regards Dinesh Pandey --
Re: [SQL] How to add 1 hour in a date or time stamp?
Dinesh, The documentation is very helpful for this topic. Typing 'add 1 hour timestamp' into the search box at: http://www.postgresql.org/docs/8.0/interactive/index.html yields the following page as the first hit: http://www.postgresql.org/docs/8.0/static/functions-datetime.html Sean - Original Message - From: Dinesh Pandey To: 'PostgreSQL' ; pgsql-general@postgresql.org Sent: Monday, April 18, 2005 9:38 AM Subject: [SQL] How to add 1 hour in a date or time stamp? How to add 1 hour in a date or time stamp? RegardsDinesh Pandey--
Re: [SQL] How to add 1 hour in a date or time stamp?
On Monday 18 April 2005 08:38 am, Dinesh Pandey wrote: > How to add 1 hour in a date or time stamp? timestamp + '1 hour'::interval is one way. -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 214-351-4152 E-Mail: ler@lerctr.org US Mail: 3535 Gaspar Drive, Dallas, TX 75220-3611 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] How to add 1 hour in a date or time stamp?
O Dinesh Pandey έγραψε στις Apr 18, 2005 : > > > How to add 1 hour in a date or time stamp? # SELECT now() + '1 hour'::interval; > > Regards > Dinesh Pandey > > > > -- > > > > > > -- -Achilleus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] outer join in ms query
MS Query is crippled. If you want to do much from Excel, you will need to write code. But it's not difficult. ADO is a good choice for this. "gad renert via DBMonster.com" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello, I am a novice with SQL, I have 2 tables > (transfer) > bar_code | quant | date > 723445...| 2 |01/03/2005 > > (imports) > > bar_code | serial_no | supplier > > > I want to add to the first table (transfer) > next to each barcode the suppliers name from the (imports) table. > > ms query doesn't allow me to do outer joins. > > I tried: > SELECT transfer.bar_code, transfer.quant, imports.supplier > FROM transfer left join imports on transfer.bar_code=imports.bar_cod > GROUP BY transfer.bar_code > > and it didn't work popperly, the (transfer) table is modified. > what i want to do is very similar to the VLOOKUP function in EXCELL > > Hope that someone can help me. > > Gadi > > -- > Message posted via http://www.dbmonster.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] outer join in ms query
Or add a view to your PG database, and simply issue a select from Excel... That faster and easier to maintain that any code you can write client-side... -Message d'origine- De : [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] De la part de Jeff Eckermann Envoyé : lundi, 18. avril 2005 16:51 À : pgsql-sql@postgresql.org Objet : Re: [SQL] outer join in ms query MS Query is crippled. If you want to do much from Excel, you will need to write code. But it's not difficult. ADO is a good choice for this. "gad renert via DBMonster.com" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hello, I am a novice with SQL, I have 2 tables > (transfer) > bar_code | quant | date > 723445...| 2 |01/03/2005 > > (imports) > > bar_code | serial_no | supplier > > > I want to add to the first table (transfer) next to each barcode the > suppliers name from the (imports) table. > > ms query doesn't allow me to do outer joins. > > I tried: > SELECT transfer.bar_code, transfer.quant, imports.supplier FROM > transfer left join imports on transfer.bar_code=imports.bar_cod GROUP > BY transfer.bar_code > > and it didn't work popperly, the (transfer) table is modified. > what i want to do is very similar to the VLOOKUP function in EXCELL > > Hope that someone can help me. > > Gadi > > -- > Message posted via http://www.dbmonster.com ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] 'Select INTO" in Execute (dynamic query )
Hi What’s wrong with this code (ERROR: syntax error at or near "INTO" at character 8)? Problem: I want to put A1, A2 values in two variables vara, varb. CREATE OR REPLACE FUNCTION test(text) RETURNS VARCHAR AS $$ Declare vara VARCHAR(10) :=''; varb VARCHAR(10) :=''; result VARCHAR(10) :='Result'; BEGIN EXECUTE( 'Select INTO vara, varb A1, A2 from '|| $1 ); RETURN result||': '|| vara ||' '|| varb; END; $$ LANGUAGE plpgsql; Regards Dinesh Pandey
Re: [SQL] [GENERAL] 'Select INTO" in Execute (dynamic query )
"Dinesh Pandey" <[EMAIL PROTECTED]> writes: > What's wrong with this code (ERROR: syntax error at or near "INTO" at > character 8)? You can't use plpgsql's SELECT INTO in an EXECUTE'd command, because SELECT INTO means something entirely different to the main SQL engine. The usual workaround is to use FOR ... IN EXECUTE. See the plpgsql docs. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] User Defined Functions Errors
How to I return an error from inside a user defined function? For example the following: CREATE or REPLACE FUNCTION drop_node (integer) RETURNS text AS ' DECLARE mleft INTEGER; mright INTEGER; BEGIN -- Check if the desired node exists SELECT lft, rgt FROM structure WHERE id = $1 INTO mleft, mright; IF mleft IS NULL THEN RETURN ''No entry found with an id of ''||$2; END IF; -- Drop the node and its subtree DELETE FROM structure WHERE lft >= mleft AND rgt <= mright; -- Close the gap UPDATE structure SET rgt = rgt - (mright - mleft + 1) WHERE rgt > mright; UPDATE structure SET lft = lft - (mright - mleft + 1) WHERE lft > mleft; RETURN ''ok''; END; ' LANGUAGE 'plpgsql'; Should be terminated with an error @ RETURN "No Entry found"; instead of returning the error text. best regards and thanks, Alex -- The mind is essential -- http://essentialmind.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] User Defined Functions Errors
On Mon, Apr 18, 2005 at 10:32:26PM +0400, A. Kulikov wrote: > > How to I return an error from inside a user defined function? Use RAISE. See "Errors and Messages" in the PL/pgSQL documentation. http://www.postgresql.org/docs/8.0/interactive/plpgsql-errors-and-messages.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] About "Alter table... alter column.. TYPE ... "
Hello, To alter table column from varchar(32) to date. "Alter table" command does not seem to work: alter table test alter column col type date ; ERROR: column "col1" cannot be cast to type "date" Tks, Emi ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] About "Alter table... alter column.. TYPE ... "
> To alter table column from varchar(32) to date. "Alter table" command > does not seem to work: > > alter table test alter column col type date ; > ERROR: column "col1" cannot be cast to type "date" Alter table will not automatically throw away information. That is, in cases where it believes you may lose data, it will not perform it. That said, you can coax it into doing so. ALTER TABLE test ALTER COLUMN col TYPE date USING CAST(col AS date); USING is an arbitrary expression capable of doing most things you can do in an UPDATE. -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [SQL] tsearch2
Dan, I don't remember what the probem you have ? Oleg On Mon, 18 Apr 2005, Dan Feiveson wrote: Hi Oleg, Still trying to get tsearch2 to work ... from archived message board it looks like our problem is that we don't have LC_CTYPE and LC_COLLATE established. We're running 7.3.4 - are there any potential pitfalls if we set LC_CTYPE and LC_COLLATE (in conjunction with other settings? -- We set the locale of C on our current configuration to allow LIKE searches to use indexes - will this be affected? Also what grants do we need to run to get a non super user working with tsearch2? Thanks again for all your help, Dan Dan Feiveson DataJoe LLC Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] Function declaration
Is there a possiblity to declare functions with optional parameters i.e. in case some parameters are not passed to the function, then some kind of default value is assigned to the function? regards, alex -- The mind is essential -- http://essentialmind.com/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Function declaration
Alex, What happens if you declare your function as: function(text,text) or whatever your datatypes for each argument are supposed to be and then, within your function, test whether the arguments are NULL? I haven't tried it, so I can't say for sure, but it seems like you should be able to pass a SQL NULL as an argument. Sean - Original Message - From: "A. Kulikov" <[EMAIL PROTECTED]> To: "Sean Davis" <[EMAIL PROTECTED]> Sent: Monday, April 18, 2005 8:44 PM Subject: Re: [SQL] Function declaration Unfortunately that is not what I am looking for =( Although the task I have in mind can be accomplished using overloading. Is there no equivalent in plpgsql for function(foo,bar=null)... ?? regards, alex 2005/4/19, Sean Davis <[EMAIL PROTECTED]>: Alex, I think what you are looking for is called function overloading. See the documenation here: http://www.postgresql.org/docs/8.0/interactive/xfunc-overload.html Sean - Original Message - From: "A. Kulikov" <[EMAIL PROTECTED]> To: Sent: Monday, April 18, 2005 8:28 PM Subject: [SQL] Function declaration Is there a possiblity to declare functions with optional parameters i.e. in case some parameters are not passed to the function, then some kind of default value is assigned to the function? -- The mind is essential -- http://essentialmind.com/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] row-attribute in EXPLAIN-output doesn't match count(*)
Alvaro Herrera wrote: It is a Postgres limitation as well. We _could_ make the server "really start the transaction" at the point the first query is issued instead of when the BEGIN is issued. And also, really finish the transaction right after the last statement is executed, instead of waiting until the COMMIT is issued :-) Carlos -- ---(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] How to Port Oracle's user defined "Package" into Postgres 8.0.1.
hi, Dinesh Pandey wrote, On 4/11/2005 15:39: Hi folks, Can any one give me an idea about: How to Port Oracle's user defined "Package" into Postgres 8.0.1. there is no Package in postgresql. You have to rewrite the logic, extract the functions from Package and recreate them for postgres. But you can't port 1:1 oracle packages to pg. C. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] CREATE USER in side a TRIGGER FUNCTION
Hi I want to put login and user managment into the database for security reasons. I have a employee table. When I add a person to the employee table I want to create them in the database and when I remove a person I want to drop the person from the database also. How do you do it and what is wrong the the following code? shaun here is the add person trigger. CREATE FUNCTION insuser () RETURNS TRIGGER AS 'BEGIN IF NEW.role = ''clerk'' THEN EXECUTE CREATE USER NEW.login IN GROUP gp_clerk; ELSIF NEW.role = ''medic'' THEN EXECUTE CREATE USER NEW.login IN GROUP gp_medic; ELSIF NEW.role = ''super'' THEN CREATE USER NEW.login IN GROUP gp_super; ELSIF NEW.role = ''admin'' THEN CREATE USER NEW.login CREATEUSER IN GROUP gp_admin; ELSIF NEW.role = ''maint'' THEN CREATE USER NEW.login CREATEUSER IN GROUP gp_maint; END IF; RETURN NEW; END;' LANGUAGE plpgsql; ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] can a function return a virtual table?
This is the question i'm telling myself. It is because we don't really delete table entries, just setting a status field to '-1'. So a valid select would look like: SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); It would be much nicer to have to write something like: SELECT xyz, abc FROM active(tablex); where the function 'active(x)' returns a virtual table with all entries from table x where status is > -1. But sadly I have no idea how write such a function. Good old O'reilly can't help (or i'm to dumb *g*). -- GnuPG-PublicKey -> http://www.hobsons.de/pgp/kai_hessing.asc Bei seinen Handlungen ist vorzubedenken besser als nachzubedenken. (Demokrit, um 460 v. Chr.) ---(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
[SQL] SQL subquery (count distinct) - Any Ideas?
I'm trying to write a query to return the number of different customers
called on a single day. The database has a table called 'user', a table
called 'caller_session' which references a 'user' record, and a table called
'call' which references a 'caller_session'. This is my current attempt:
select
user.name,
sum((select count(distinct call.customer_id) from call where
call.caller_session_id=cs.caller_session_id)) as contacted
from user, caller_session cs
where cs.user_id=user.user_id
and date(cs.session_date) = date('2005-04-13')
group by user.name;
I get back a list of names and a call count (as desired), but the count is
not correct! The subqery is counting the number of different customers
called for each caller_session, and summing them based on the user.name
field. If the same customer is called in 2 different sessions, this is
being counted as 2 customers, but should be 1!
The 'contacted' count calculation has to be in a subquery as above, because
there are other subqueries after this one to calculate other data based on
the outer query (the outer query has to be as it is here). Not quite sure
where the sum, count and distinct should really go to get the required
results!
Any help greatly appreciated!
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] SQL group select question
I have a table with a unique id, a name, a number and a value, for example: id name numb value -- - 1 tom 101000 2 dick 102000 3 harry 103000 4 dick 214000 5 harry 215000 6 harry 326000 As you can see, tom has 1 entry, dick has 2 and harry has 3. I would like to select 3 records (one for each tom, dick and harry), based on the associated number (id 1 should be chosen, because there is no other 'tom' record, id 4 should be chosen over id 2, because 21 > 10, and id 6 over ids 3 and 5, because 32 > 10 or 21). The value column is the data that I am interested in: SELECT t.id, t.value FROM (SELECT name, MAX(numb) as numb FROM test1) AS s JOIN test1 t ON (t.name = s.name AND t.numb = s.numb); id value -- - 6 6000 4 4000 1 1000 Cheers, Ezequiel Tolnay [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] tsearch2
Hi Oleg, Still trying to get tsearch2 to work ... from archived message board it looks like our problem is that we don't have LC_CTYPE and LC_COLLATE established. We're running 7.3.4 - are there any potential pitfalls if we set LC_CTYPE and LC_COLLATE (in conjunction with other settings? -- We set the locale of C on our current configuration to allow LIKE searches to use indexes - will this be affected? Also what grants do we need to run to get a non super user working with tsearch2? Thanks again for all your help, Dan Dan Feiveson DataJoe LLC
Re: [SQL] Getting the output of a function used in a where clause
On Tue, 2005-04-12 at 23:08 -0700, Bill Lawrence wrote: > Thanks, > > Unfortunately, I think that solution requires the distance calculation to be > executed twice for each record in the table. There are ~70K records in the > table. Is the postgres query optimizer smart enough to only perform the > calculation once? It is in some places, but possibly not in that one. You can force it with a subselect though: SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes) AS tab where distance <= $dist; > Bill > > > > -Original Message- > From: Scott Marlowe [mailto:[EMAIL PROTECTED] > Sent: Tuesday, April 12, 2005 6:55 AM > To: Bill Lawrence > Cc: PFC; pgsql-sql@postgresql.org > Subject: Re: [SQL] Getting the output of a function used in a where clause > > Why not just do: > > SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from > zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;"; > > > On Mon, 2005-04-11 at 20:25, Bill Lawrence wrote: > > Boy I sure thought that would work... I received the following from > postgres: > > > > ERROR: Attribute "distance" not found. > > > > Started looking into gist Looks complex. > > > > Any other ideas? > > > > > > -Original Message- > > From: PFC [mailto:[EMAIL PROTECTED] > > Sent: Monday, April 11, 2005 1:51 AM > > To: Bill Lawrence; pgsql-sql@postgresql.org > > Subject: Re: [SQL] Getting the output of a function used in a where clause > > > > > > try: > > > > SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes > > where distance <= $dist;"; > > > > OR you could use a gist index with a geometric datatype to get it a lot > > faster. > > > > > > On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]> > > wrote: > > > > > HI, > > > > > > I'm a newbie so please bear with me. I have a function defined (got it > > > from > > > one of your threads... thanks Joe Conway) which calculates the distance > > > between 2 zip code centeroids (in lat,long). This thing works great. > > > However, I want to sort my results by distance without incurring the > > > additional burden of executing the function twice. A simplified version > > > of > > > my current SQL (written in a perl cgi) that returns a set of zip codes > > > within a given radius is: > > > > > > > > > What I want to write is something like: > > > > > > $sql = "SELECT zipcode, distance from zipcodes where distance <= $dist > > > order > > > by distance;"; > > > > > > But I don't the magic SQL phrase to populate the distance variable using > > > my > > > nifty function. Do I need to create an output type for distance? > > > > > > Thanks in advance! > > > > > > Bill > > > > > > > > > > > > > > > > > > > ---(end of broadcast)--- > > TIP 7: don't forget to increase your free space map settings > > > > ---(end of broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > -- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Getting the output of a function used in a where clause
Thanks, Unfortunately, I think that solution requires the distance calculation to be executed twice for each record in the table. There are ~70K records in the table. Is the postgres query optimizer smart enough to only perform the calculation once? Bill -Original Message- From: Scott Marlowe [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 12, 2005 6:55 AM To: Bill Lawrence Cc: PFC; pgsql-sql@postgresql.org Subject: Re: [SQL] Getting the output of a function used in a where clause Why not just do: SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes where zipdist($lat1d,$lon1d,lat,long) <= $dist;"; On Mon, 2005-04-11 at 20:25, Bill Lawrence wrote: > Boy I sure thought that would work... I received the following from postgres: > > ERROR: Attribute "distance" not found. > > Started looking into gist Looks complex. > > Any other ideas? > > > -Original Message- > From: PFC [mailto:[EMAIL PROTECTED] > Sent: Monday, April 11, 2005 1:51 AM > To: Bill Lawrence; pgsql-sql@postgresql.org > Subject: Re: [SQL] Getting the output of a function used in a where clause > > > try: > > SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes > where distance <= $dist;"; > > OR you could use a gist index with a geometric datatype to get it a lot > faster. > > > On Sat, 09 Apr 2005 03:43:39 +0200, Bill Lawrence <[EMAIL PROTECTED]> > wrote: > > > HI, > > > > I'm a newbie so please bear with me. I have a function defined (got it > > from > > one of your threads... thanks Joe Conway) which calculates the distance > > between 2 zip code centeroids (in lat,long). This thing works great. > > However, I want to sort my results by distance without incurring the > > additional burden of executing the function twice. A simplified version > > of > > my current SQL (written in a perl cgi) that returns a set of zip codes > > within a given radius is: > > > > > > What I want to write is something like: > > > > $sql = "SELECT zipcode, distance from zipcodes where distance <= $dist > > order > > by distance;"; > > > > But I don't the magic SQL phrase to populate the distance variable using > > my > > nifty function. Do I need to create an output type for distance? > > > > Thanks in advance! > > > > Bill > > > > > > > > > > > ---(end of broadcast)--- > TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] SQL subquery (count distinct) - Any Ideas?
On Wed, 2005-04-13 at 18:28 +0100, Matt Fulford wrote:
> I'm trying to write a query to return the number of different customers
> called on a single day. The database has a table called 'user', a table
> called 'caller_session' which references a 'user' record, and a table called
> 'call' which references a 'caller_session'. This is my current attempt:
If I understand correct, this might work and still allow you to keep
your other subselects:
SELECT tab.name,
sum(contacted) AS contacted,
... subselects ...
FROM (SELECT user_id, user.name, count(*) AS contacted
FROM user JOIN caller_session USING (user_id)
JOIN call USING (caller_session_id)
WHERE date(cs.session_date) = date('2005-04-13')
GROUP BY user_id, user.name) AS tab
JOIN caller_session USING (user_id)
GROUP BY user_id;
> select
> user.name,
> sum((select count(distinct call.customer_id) from call where
> call.caller_session_id=cs.caller_session_id)) as contacted
> from user, caller_session cs
> where cs.user_id=user.user_id
> and date(cs.session_date) = date('2005-04-13')
> group by user.name;
>
> I get back a list of names and a call count (as desired), but the count is
> not correct! The subqery is counting the number of different customers
> called for each caller_session, and summing them based on the user.name
> field. If the same customer is called in 2 different sessions, this is
> being counted as 2 customers, but should be 1!
>
> The 'contacted' count calculation has to be in a subquery as above, because
> there are other subqueries after this one to calculate other data based on
> the outer query (the outer query has to be as it is here). Not quite sure
> where the sum, count and distinct should really go to get the required
> results!
>
> Any help greatly appreciated!
>
>
>
>
> ---(end of broadcast)---
> TIP 8: explain analyze is your friend
>
--
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [SQL] can a function return a virtual table?
On Wed, Apr 13, 2005 at 03:44:25PM +0200, Kai Hessing wrote: > > This is the question i'm telling myself. It is because we don't really > delete table entries, just setting a status field to '-1'. So a valid > select would look like: SELECT xyz, abc FROM (SELECT * FROM tablex WHERE > status > -1); I'll pick a nit and point out that the above isn't a valid query: test=> SELECT xyz, abc FROM (SELECT * FROM tablex WHERE status > -1); ERROR: subquery in FROM must have an alias HINT: For example, FROM (SELECT ...) [AS] foo. In this simple example you could omit the subquery altogether: SELECT xyz, abc FROM tablex WHERE status > -1; > It would be much nicer to have to write something like: SELECT xyz, abc > FROM active(tablex); where the function 'active(x)' returns a virtual > table with all entries from table x where status is > -1. But sadly I > have no idea how write such a function. Good old O'reilly can't help (or > i'm to dumb *g*). See the documentation for writing set-returning functions (SRFs). The following links should get you started (if you're using a version of PostgreSQL older than 8.0, then see the documentation for that version): http://www.postgresql.org/docs/8.0/interactive/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS http://www.postgresql.org/docs/8.0/interactive/xfunc-sql.html#AEN29503 http://www.postgresql.org/docs/8.0/interactive/plpgsql-control-structures.html#AEN32823 Another possibility would be to use views. You'd need to create a view on each table. http://www.postgresql.org/docs/8.0/interactive/tutorial-views.html http://www.postgresql.org/docs/8.0/interactive/sql-createview.html Yet another possibility would be to move the inactive rows to a separate table. You could reconstruct the original table with a UNION of the active and inactive tables. http://www.postgresql.org/docs/8.0/interactive/queries-union.html -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] Getting the output of a function used in a where clause
Rod Taylor <[EMAIL PROTECTED]> writes: > You can force it with a subselect though: > SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) > as distance > from zipcodes) AS tab where distance <= $dist; The above will *not* stop zipdist from being run twice, because the planner will happily flatten the subquery into the outer query, resulting in the same situation of zipdist() being present twice in the text of the query. You could force the issue with SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist; since LIMIT/OFFSET clauses presently disable the flattening optimization. Keep in mind though that the OFFSET is an absolute optimization fence: it will result in the subquery being evaluated completely, even if there were outer conditions that might have avoided the need to look at some rows. For example, if the query is SELECT * FROM (SELECT zipcode, zipdist($lat1d,$lon1d,lat,long) as distance from zipcodes OFFSET 0) AS tab where distance <= $dist AND some-other-conditions then not letting the some-other-conditions migrate down below the evaluation of zipdist could result in making the query be far slower, not faster, than you would get if you weren't trying to outsmart the planner. In general the system operates on the assumption that function calls are cheap relative to disk I/O. If that's not true for you, you're going to have some issues ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [SQL] CREATE USER in side a TRIGGER FUNCTION
On Thu, Apr 14, 2005 at 08:30:02PM -0700, shaun wrote: > > I want to put login and user managment into the database for security > reasons. I have a employee table. When I add a person to the employee > table I want to create them in the database and when I remove a person I > want to drop the person from the database also. How do you do it and > what is wrong the the following code? > > CREATE FUNCTION insuser () RETURNS TRIGGER AS > 'BEGIN >IF NEW.role = ''clerk'' THEN >EXECUTE CREATE USER NEW.login IN GROUP gp_clerk; EXECUTE needs a string. Try something like this: EXECUTE ''CREATE USER '' || quote_ident(NEW.login) || '' IN GROUP gp_clerk''; quote_ident() is a safety measure to prevent SQL injection. For more information about using EXECUTE, see "Executing Dynamic Commands" in the PL/pgSQL documentation (if you're using a version of PostgreSQL earlier than 8.0 then see the documentation for that version): http://www.postgresql.org/docs/8.0/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
