Thread: "select ..... for update of ..." doesn't support full qualified table name?

CREATE SCHEMA one;
CREATE TABLE one.aa ( a INT );
CREATE SCHEMA two;
CREATE TABLE two.bb ( b INT );
SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF one.aa;

ERROR:  syntax error at or near "." at character 73 (points to the
last instance of "one.aa" in SQL query)

p.s. in our application we actually have the same table names but in
different schemas, so avoiding using of schema name in table reference
is not possible, so actual select looks like this:

CREATE TABLE one.t ( a INT );
CREATE TABLE two.t ( b INT );
SELECT * FROM one.t, two.t WHERE one.t.a = two.t.b FOR UPDATE OF one.t;

--
Vlad

yes, we actually use table alias as a workaround, I thought that it's
actually looks like error in postgresql parser (or deeper) that needs
to be reported.

thanks.

On 9/6/05, Matt Miller <mattm@epx.com> wrote:
> On Tue, 2005-09-06 at 13:45 -0400, Vlad wrote:
> > SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF
> > one.aa;
> >
> > ERROR:  syntax error at or near "." at character 73 (points to the
> > last instance of "one.aa" in SQL query
>
> Try using a table alias, and reference that alias in the "for update of"
> clause.
>

--
Vlad

On Tue, 2005-09-06 at 13:45 -0400, Vlad wrote:
> SELECT * FROM one.aa, two.bb WHERE one.aa.a = two.bb.b FOR UPDATE OF
> one.aa;
>
> ERROR:  syntax error at or near "." at character 73 (points to the
> last instance of "one.aa" in SQL query

Try using a table alias, and reference that alias in the "for update of"
clause.

Vlad <marchenko@gmail.com> writes:
> yes, we actually use table alias as a workaround, I thought that it's
> actually looks like error in postgresql parser (or deeper) that needs
> to be reported.

No, it's supposed to be that way: FOR UPDATE items are table aliases.
Perhaps this isn't adequately documented...

            regards, tom lane

Tom,

yes, this part is not well documented - specially double checked
before sendin email to the list.

Though question is - doesn't it seem logical to be able to use full
table names in FOR UPDATE part like I can use them in WHERE part (if I
don't need/want to use an alias)? Is it something postgresql speciffic
or it's SQL standard (pardon my ignorance)?

> > yes, we actually use table alias as a workaround, I thought that it's
> > actually looks like error in postgresql parser (or deeper) that needs
> > to be reported.
>
> No, it's supposed to be that way: FOR UPDATE items are table aliases.
> Perhaps this isn't adequately documented...

--
Vlad

Vlad <marchenko@gmail.com> writes:
> Though question is - doesn't it seem logical to be able to use full
> table names in FOR UPDATE part like I can use them in WHERE part (if I
> don't need/want to use an alias)? Is it something postgresql speciffic
> or it's SQL standard (pardon my ignorance)?

The entire construct is Postgres-specific, so you can't really point
to the spec and say it's wrong.

I don't see any merit whatever in the "I shouldn't need to use an alias"
argument.  If you don't have unique aliases then you're going to have
problems anyway.

            regards, tom lane