Thread: Aliased table names ...oddity?

Aliased table names ...oddity?

From
Steve Tucknott
Date:
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

Re: Aliased table names ...oddity?

From
Michael Glaesemann
Date:
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



Re: Aliased table names ...oddity?

From
Steve Tucknott
Date:
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

Re: Aliased table names ...oddity?

From
"Obe, Regina DND\\MIS"
Date:
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

Re: Aliased table names ...oddity?

From
Tom Lane
Date:
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

Re: Aliased table names ...oddity?

From
Michael Glaesemann
Date:
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



Re: Aliased table names ...oddity?

From
Date:
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

User Defined Note Order Problem

From
Date:
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/

Re: User Defined Note Order Problem

From
Date:
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

Re: User Defined Note Order Problem

From
Bruno Wolff III
Date:
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.