Thread: How to recognize PG SQL files?

I'm writing a syntax mode for PG for Vim (posted an early version
earlier today) and would like to have Vim recognize that this is a PG
SQL file (rather than a MySQL file or an Oracle file or such).

I'm trying to brainstorm what the unique-looking parts of PG's syntax
are. These need to be present in PG SQL files (& hopefully not too
obscure) but not present in other DB SQL files.

The PG manual states how PG differs from SQL standards, but not how it
differs from other popular databases. I've used MySQL and Oracle in the
past, but not recently, and haven't use DB2 or SQLServer in ages and
don't have docs for them anymore.

I have a few possible suggestions. Can anyone:

* tell me if these are used in other DB systems (& shouldn't be part of
my syntax)

or

* provide other ideas for unique PG syntax


My ideas:

* \connect

* template1

* "from pg_" (selecting from a PG system table)

* "create rule"

* plpgsql, plperl, plpython, pltcl, pltclu, plruby (& now plphp, too, I
suppose! ;) )

* "nextval(", "currval("


I'd love to find something common, like "SERIAL" or "CREATE SEQUENCE" or
such, but I suspect that other commonly-used databases use these.

Thanks for any help!

On Thu, Aug 07, 2003 at 10:04:22AM +0100, Tim McAuley wrote:
> There are syntax files for plpgsql available for textpad. You could
> check them out to see if they might help.
>
> http://www.textpad.com/add-ons/syna2g.html

A great idea, Tim.

Unfortunately, though, it doesn't seem as if the how-to-recognize logic
is part of TextPad's syntax files. The files are the keywords of the
language (which I've already done for Vim), but not ideas on how to tell
that the file you're looking at is PG SQL.

Getting these TextPad files will help me cross-check that I haven't
missed any keywords for PG, though, so it's still very helpful.

Thanks!

- j.

Perhaps the simplest thing would be to have
people start out the files with
    -- pgsql

If they didn't want to do that, they could set the
syntax by hand.

On the other hand, I just put my pgsql syntax
into the sql syntax file :-)

There is no guarantee that any particular pgsqlism
is in any sql script.

elein

PS: If you get the syntax working for function definitions,
I and many others will owe you undying gratitude.

On Wed, Aug 06, 2003 at 12:55:52PM -0400, Joel Burton wrote:
> I'm writing a syntax mode for PG for Vim (posted an early version
> earlier today) and would like to have Vim recognize that this is a PG
> SQL file (rather than a MySQL file or an Oracle file or such).
>
> I'm trying to brainstorm what the unique-looking parts of PG's syntax
> are. These need to be present in PG SQL files (& hopefully not too
> obscure) but not present in other DB SQL files.
>
> The PG manual states how PG differs from SQL standards, but not how it
> differs from other popular databases. I've used MySQL and Oracle in the
> past, but not recently, and haven't use DB2 or SQLServer in ages and
> don't have docs for them anymore.
>
> I have a few possible suggestions. Can anyone:
>
> * tell me if these are used in other DB systems (& shouldn't be part of
> my syntax)
>
> or
>
> * provide other ideas for unique PG syntax
>
>
> My ideas:
>
> * \connect
>
> * template1
>
> * "from pg_" (selecting from a PG system table)
>
> * "create rule"
>
> * plpgsql, plperl, plpython, pltcl, pltclu, plruby (& now plphp, too, I
> suppose! ;) )
>
> * "nextval(", "currval("
>
>
> I'd love to find something common, like "SERIAL" or "CREATE SEQUENCE" or
> such, but I suspect that other commonly-used databases use these.
>
> Thanks for any help!
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Thanks very much for taking this task up. Can you pl. mention where did you
upload the version of the syntax file that you are talking about ? Is it on the
www.vim.org site or www.postgresql.org. I tried looking into the vim.org
repository but could not find one.

Thanks, in advance.

Bhavesh

--- Joel Burton <joel@joelburton.com> wrote:
> I'm writing a syntax mode for PG for Vim (posted an early version
> earlier today) and would like to have Vim recognize that this is a PG
> SQL file (rather than a MySQL file or an Oracle file or such).
>
> I'm trying to brainstorm what the unique-looking parts of PG's syntax
> are. These need to be present in PG SQL files (& hopefully not too
> obscure) but not present in other DB SQL files.
>
> The PG manual states how PG differs from SQL standards, but not how it
> differs from other popular databases. I've used MySQL and Oracle in the
> past, but not recently, and haven't use DB2 or SQLServer in ages and
> don't have docs for them anymore.
>
> I have a few possible suggestions. Can anyone:
>
> * tell me if these are used in other DB systems (& shouldn't be part of
> my syntax)
>
> or
>
> * provide other ideas for unique PG syntax
>
>
> My ideas:
>
> * \connect
>
> * template1
>
> * "from pg_" (selecting from a PG system table)
>
> * "create rule"
>
> * plpgsql, plperl, plpython, pltcl, pltclu, plruby (& now plphp, too, I
> suppose! ;) )
>
> * "nextval(", "currval("
>
>
> I'd love to find something common, like "SERIAL" or "CREATE SEQUENCE" or
> such, but I suspect that other commonly-used databases use these.
>
> Thanks for any help!
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org


__________________________________
Do you Yahoo!?
SBC Yahoo! DSL - Now only $29.95 per month!
http://sbc.yahoo.com

On Wed, Aug 06, 2003 at 12:55:52PM -0400, Joel Burton wrote:

Reposting, with some clarification to my request. Thanks to the several
responses I received originally.

Yes, I know that a perfectly vaild PGSQL SQL file could contain only
ANSI SQL and therefore not be recognized as PG-related. In that case,
though, it would be recognized by Vim's ANSI SQL coloring, and given
that's all this file contains, that's no problem. ;)

However, given that many people edit pg_dump files (which do contain
pgsql-isms in most cases), and many other people do use non-ANSI PG
features, I figure that I should be able to recognize 95% of the files,
and that's a win since it will consistent highlight PG syntax and make
it easier to scan files, catch typos, etc.

Some easy things:

* if a filename ends with ".pgsql", it will use PG syntax coloring

* if a file contains a comment with "pgsql" in it in the first few
  lines, it will use PG syntax coloring

* if a file contains the comments that pg_dump puts in a file, it will
  use PG syntax coloring.

I'd still like to catch other cases, and still have the following
questions: what features among our extensions are unique to us, and what
features are used by other common DBs? People that have more recent
experience with MySQL, Oracle, SQLServer, etc. can probably answet this
question.

Thanks, everyone!

- j.

> I'm writing a syntax mode for PG for Vim (posted an early version
> earlier today) and would like to have Vim recognize that this is a PG
> SQL file (rather than a MySQL file or an Oracle file or such).
>
> I'm trying to brainstorm what the unique-looking parts of PG's syntax
> are. These need to be present in PG SQL files (& hopefully not too
> obscure) but not present in other DB SQL files.
>
> The PG manual states how PG differs from SQL standards, but not how it
> differs from other popular databases. I've used MySQL and Oracle in the
> past, but not recently, and haven't use DB2 or SQLServer in ages and
> don't have docs for them anymore.
>
> I have a few possible suggestions. Can anyone:
>
> * tell me if these are used in other DB systems (& shouldn't be part of
> my syntax)
>
> or
>
> * provide other ideas for unique PG syntax
>
>
> My ideas:
>
> * \connect
>
> * template1
>
> * "from pg_" (selecting from a PG system table)
>
> * "create rule"
>
> * plpgsql, plperl, plpython, pltcl, pltclu, plruby (& now plphp, too, I
> suppose! ;) )
>
> * "nextval(", "currval("
>
>
> I'd love to find something common, like "SERIAL" or "CREATE SEQUENCE" or
> such, but I suspect that other commonly-used databases use these.

--

Joel BURTON  |  joel@joelburton.com  |  joelburton.com  |  aim: wjoelburton
Independent Knowledge Management Consultant