Re: [SQL] COUNT(*) to find records which have a certain number of dependencies ? - Mailing list pgsql-patches

T E Schmitz <mailreg@numerixtechnology.de> writes:

> There's a German saying "Go and find a parking-meter", i.e. suggesting to pop a
> coin in the parking-meter and talk to it as nobody else wants to listen. ;-)

Yes well I anticipated such a response. So I tried my hand at it myself.

Well I finally found a problem tractable enough for me to get all the way from
start to end in a single sitting. Here's a simple solution to my complaint.

This patch allows subqueries without aliases. This is SQL-non-spec-compliant
syntax that Oracle supports and many users expect to work. It's also just
damned convenient, especially for simple ad-hoc queries.

There was a comment saying an alias name would have to be constructed so I
took that approach. It seems like it would have been cleaner to just ensure
that the code doesn't fail when no alias is present. But I have no idea how
much work would be involved in that, so I just took advice from the anonymous
author of the comment.

Incidentally, It seems weird to me that the counter doesn't reset for every
query. Perhaps I should change that?


Index: src/backend/parser/gram.y
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.475
diff -u -p -c -r2.475 gram.y
cvs diff: conflicting specifications of output style
*** src/backend/parser/gram.y    29 Aug 2004 04:12:35 -0000    2.475
--- src/backend/parser/gram.y    20 Sep 2004 21:34:13 -0000
*************** table_ref:    relation_expr
*** 5158,5177 ****
                  {
                      /*
                       * The SQL spec does not permit a subselect
!                      * (<derived_table>) without an alias clause,
!                      * so we don't either.  This avoids the problem
!                      * of needing to invent a unique refname for it.
!                      * That could be surmounted if there's sufficient
!                      * popular demand, but for now let's just implement
!                      * the spec and see if anyone complains.
!                      * However, it does seem like a good idea to emit
!                      * an error message that's better than "syntax error".
                       */
!                     ereport(ERROR,
!                             (errcode(ERRCODE_SYNTAX_ERROR),
!                              errmsg("subquery in FROM must have an alias"),
!                              errhint("For example, FROM (SELECT ...) [AS] foo.")));
!                     $$ = NULL;
                  }
              | select_with_parens alias_clause
                  {
--- 5158,5172 ----
                  {
                      /*
                       * The SQL spec does not permit a subselect
!                      * (<derived_table>) without an alias clause, We surmount
!                      * this because of popular demand by gining up a fake name
!                      * in transformRangeSubselect
                       */
!
!                     RangeSubselect *n = makeNode(RangeSubselect);
!                     n->subquery = $1;
!                     n->alias = NULL;
!                     $$ = (Node *) n;
                  }
              | select_with_parens alias_clause
                  {
Index: src/backend/parser/parse_clause.c
===================================================================
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/parse_clause.c,v
retrieving revision 1.136
diff -u -p -c -r1.136 parse_clause.c
cvs diff: conflicting specifications of output style
*** src/backend/parser/parse_clause.c    29 Aug 2004 05:06:44 -0000    1.136
--- src/backend/parser/parse_clause.c    20 Sep 2004 21:34:14 -0000
*************** transformRangeSubselect(ParseState *psta
*** 418,426 ****
       * an unlabeled subselect.
       */
      if (r->alias == NULL)
!         ereport(ERROR,
!                 (errcode(ERRCODE_SYNTAX_ERROR),
!                  errmsg("subquery in FROM must have an alias")));

      /*
       * Analyze and transform the subquery.
--- 418,434 ----
       * an unlabeled subselect.
       */
      if (r->alias == NULL)
!     {
!         static int subquery_counter = 1;
!         static char buf[30];
!
!         sprintf(buf, "*SUBQUERY*%d*", subquery_counter++);
!
!         r->alias = makeNode(Alias);
!         r->alias->aliasname = pstrdup(buf);
!         r->alias->colnames = NULL;
!     }
!

      /*
       * Analyze and transform the subquery.