Thread: Aliased table names ...oddity?
PostgreSQL-7.4.5
I don't know whether this is a 'bug' (it seems like designed behaviour)
- but if you do:
SELECT markUp.*
FROM markUp AS mark
JOIN clientBranch AS clntB
ON mark.foreignRecNo = clntB.clientRecNo
AND clntB.recNo = 2
WHERE mark.serviceCoBranchRecNo = 2
AND mark.foreignTableName = 'client'
So the table has an ALIAS that is ignored in the SELECT clause, then the
WHERE clause is 'ignored' and all rows from the table are returned.
Obviously its a typo in the original SQL - (but one that's very
difficult to pick up unless the data returned is obviously outside
limits). Running the SQL in PSQL, it seems that a FROM clause is
automatically appended for the missing 'markUp' table - so it does seem
that this is intentional. Is there a way to have this 'rejected' as an
error - ie selecting from a table not included in the query?
--
Regards,
Steve Tucknott
ReTSol Ltd
DDI 01903 828769
MOBILE 07736715772
___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com
On Jun 30, 2005, at 3:54 PM, Steve Tucknott wrote: > PostgreSQL-7.4.5 <snip /> > Running the SQL in PSQL, it seems that a FROM clause is > automatically appended for the missing 'markUp' table - so it does > seem > that this is intentional. Is there a way to have this 'rejected' as an > error - ie selecting from a table not included in the query? Yes, there is/was designed behavior, and there is a way to shut it off in postgresql.conf. It's still there for backwards compatibility, but more recent versions display a notice when a FROM clause is added. test=# select foo.foo_id, bar_name from foo f join bar using (foo_id); NOTICE: adding missing FROM-clause entry for table "foo" Here are some references: http://www.postgresql.org/docs/7.4/interactive/runtime-config.html http://sql-info.de/postgresql/postgres-gotchas.html#1_5 Hope this helps. Michael Glaesemann grzm myrealbox com
Ignore this. Just seen the post re MySQL/PostgreSQL 'gotchas' - and this is one of them - with the answer that I was after. On Thu, 2005-06-30 at 07:54, Steve Tucknott wrote: > PostgreSQL-7.4.5 > > I don't know whether this is a 'bug' (it seems like designed behaviour) > - but if you do: > SELECT markUp.* > FROM markUp AS mark > JOIN clientBranch AS clntB > ON mark.foreignRecNo = clntB.clientRecNo > AND clntB.recNo = 2 > WHERE mark.serviceCoBranchRecNo = 2 > AND mark.foreignTableName = 'client' > > > So the table has an ALIAS that is ignored in the SELECT clause, then the > WHERE clause is 'ignored' and all rows from the table are returned. > Obviously its a typo in the original SQL - (but one that's very > difficult to pick up unless the data returned is obviously outside > limits). Running the SQL in PSQL, it seems that a FROM clause is > automatically appended for the missing 'markUp' table - so it does seem > that this is intentional. Is there a way to have this 'rejected' as an > error - ie selecting from a table not included in the query? > > > -- > > > Regards, > > Steve Tucknott > ReTSol Ltd > > DDI 01903 828769 > MOBILE 07736715772 > > > > > > ___________________________________________________________ > Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- Regards, Steve Tucknott ReTSol Ltd DDI 01903 828769 MOBILE 07736715772 ___________________________________________________________ Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with voicemail http://uk.messenger.yahoo.com
Yes - in the postgresql.conf there is an "add_missing_from" option - change
that to false. I think by default it is on, and in future versions they are
planning to set that to false.
-----Original Message-----
From: Steve Tucknott [mailto:steve@retsol.co.uk]
Sent: Thursday, June 30, 2005 2:55 AM
To: PostGreSQL
Subject: [NOVICE] Aliased table names ...oddity?
PostgreSQL-7.4.5
I don't know whether this is a 'bug' (it seems like designed behaviour)
- but if you do:
SELECT markUp.*
FROM markUp AS mark
JOIN clientBranch AS clntB
ON mark.foreignRecNo = clntB.clientRecNo
AND clntB.recNo = 2
WHERE mark.serviceCoBranchRecNo = 2
AND mark.foreignTableName = 'client'
So the table has an ALIAS that is ignored in the SELECT clause, then the
WHERE clause is 'ignored' and all rows from the table are returned.
Obviously its a typo in the original SQL - (but one that's very difficult to
pick up unless the data returned is obviously outside limits). Running the
SQL in PSQL, it seems that a FROM clause is automatically appended for the
missing 'markUp' table - so it does seem that this is intentional. Is there
a way to have this 'rejected' as an error - ie selecting from a table not
included in the query?
--
Regards,
Steve Tucknott
ReTSol Ltd
DDI 01903 828769
MOBILE 07736715772
___________________________________________________________
Yahoo! Messenger - NEW crystal clear PC to PC calling worldwide with
voicemail http://uk.messenger.yahoo.com
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Michael Glaesemann <grzm@myrealbox.com> writes:
> Yes, there is/was designed behavior, and there is a way to shut it
> off in postgresql.conf. It's still there for backwards compatibility,
> but more recent versions display a notice when a FROM clause is added.
BTW, as of 8.1 add_missing_from will be OFF in the default
configuration.
regards, tom lane
On Jun 30, 2005, at 10:16 PM, Tom Lane wrote: > BTW, as of 8.1 add_missing_from will be OFF in the default > configuration. Great to hear! Michael Glaesemann grzm myrealbox com
i think this is related to a pgsql gotcha... see 1.1... http://sql-info.de/postgresql/postgres-gotchas.html --- "Obe, Regina DND\\MIS" <robe.dnd@cityofboston.gov> wrote: > Yes - in the postgresql.conf there is an > "add_missing_from" option - change > that to false. I think by default it is on, and in > future versions they are > planning to set that to false. > > -----Original Message----- > From: Steve Tucknott [mailto:steve@retsol.co.uk] > Sent: Thursday, June 30, 2005 2:55 AM > To: PostGreSQL > Subject: [NOVICE] Aliased table names ...oddity? > > > PostgreSQL-7.4.5 > > I don't know whether this is a 'bug' (it seems like > designed behaviour) > - but if you do: > SELECT markUp.* > FROM markUp AS mark > JOIN clientBranch AS clntB > ON mark.foreignRecNo = clntB.clientRecNo > AND clntB.recNo = 2 > WHERE mark.serviceCoBranchRecNo = 2 > AND mark.foreignTableName = 'client' > > > So the table has an ALIAS that is ignored in the > SELECT clause, then the > WHERE clause is 'ignored' and all rows from the > table are returned. > Obviously its a typo in the original SQL - (but one > that's very difficult to > pick up unless the data returned is obviously > outside limits). Running the > SQL in PSQL, it seems that a FROM clause is > automatically appended for the > missing 'markUp' table - so it does seem that this > is intentional. Is there > a way to have this 'rejected' as an error - ie > selecting from a table not > included in the query? > > > -- > > > Regards, > > Steve Tucknott > ReTSol Ltd > > DDI 01903 828769 > MOBILE 07736715772 > > > > > > ___________________________________________________________ > > Yahoo! Messenger - NEW crystal clear PC to PC > calling worldwide with > voicemail http://uk.messenger.yahoo.com > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please > send an appropriate > subscribe-nomail command to > majordomo@postgresql.org so that your > message can get through to the mailing list > cleanly > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
i have an order column in my table to display instruction notes in the desired order. one note is no problem - of course that note is first. if only life stopped at one note! -lol- adding a second note, though, is causing me some problems. eg, the 2nd note may need to be displayed 1st or 2nd. the code is pretty straight forward if it is to be displayed 2nd. if the second note needs to be displayed first, though, the code is more challenging. i will display both notes with with the 2nd note coming 2nd. the user will see the notes and then determine the newly entered note currently displayed 2nd will need to be displayed 1st. i need to bump the order number +1 for every order number >= the required order number of newly entered note (in this specific case, that would be 1 b/c the first note is being bumped. 1+1 would leave order number 2 for the existing note and the newly entered note would have order number 1). i suspect i can do this in php once i have grabbed the order number of the new note from the user. however, is this an application for stored procedures or triggers? i'm off to read the manual now. should i use php or pgsql's functionality to get this done (if possible)? i would appreciate any available insight. thank you. ____________________________________________________ Sell on Yahoo! Auctions no fees. Bid on great items. http://auctions.yahoo.com/
i've decided this is a good application for a trigger (i hope this is right - let me know if it isn't). i want set all the order_number values >= $user_order_input to order_value + 1. i added plpsql to my db by using... createlang plpgsql db_name i saw a new function in pgadmin3 named plpgsql_call_handler() i think my function should look as follows... CREATE OR REPLACE FUNCTION func_reorder($user_order_input) RETURNS opaque AS ' BEGIN; UPDATE tablename SET order_number = order_number + 1 WHERE order_number >= $user_order_input; RETURN NEW; END; ' LANGUAGE 'plpgsql'; do i need to return anything or can i ommit the two times return is mentioned? i based this function on a book i have (of course, their example is different than my needs so it may be apples to oranges). in pgadmin3, it defaults to CREATE FUNCTION... instead of CREATE OR REPLACE FUNCTION. is there any way to change or update this? will $user_order_input pass into the function given the example above? once i nail down the correct syntax for the function, i can work on the trigger. tia... __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Please don't reply to messages to start new threads. On Wed, Jul 06, 2005 at 09:56:13 -0700, operationsengineer1@yahoo.com wrote: > i have an order column in my table to display > instruction notes in the desired order. > > one note is no problem - of course that note is first. > if only life stopped at one note! -lol- > > adding a second note, though, is causing me some > problems. eg, the 2nd note may need to be displayed > 1st or 2nd. the code is pretty straight forward if it > is to be displayed 2nd. > > if the second note needs to be displayed first, > though, the code is more challenging. > > i will display both notes with with the 2nd note > coming 2nd. the user will see the notes and then > determine the newly entered note currently displayed > 2nd will need to be displayed 1st. > > i need to bump the order number +1 for every order > number >= the required order number of newly entered > note (in this specific case, that would be 1 b/c the > first note is being bumped. 1+1 would leave order > number 2 for the existing note and the newly entered > note would have order number 1). Another option is to use a "numeric" column to track ordering. Then you can relatively easily insert a row between two existing rows. This can still have problems if you have pathalogical cases since numeric is limited to 1000 decimal digits. But under reasonable usage patterns this should work pretty well.