Обсуждение: ecpg rejects input parameters
PostgreSQL uses a :colon syntax for parameterizing SQL commands with command line variables.
create-database.sql:
CREATE DATABASE :db;
Usage:
$ psql -f create-database.sql -v db=test
However, pgsanity/ecpg rejects these.
$ pgsanity create-database.sql
line 1: ERROR: syntax error at or near ":db"
Is there a flag I can give to ecpg to ignore input parameters?create-database.sql:
CREATE DATABASE :db;
Usage:
$ psql -f create-database.sql -v db=test
However, pgsanity/ecpg rejects these.
$ pgsanity create-database.sql
line 1: ERROR: syntax error at or near ":db"
Is there a patch we could make to ecpg to accept input parameters?
On 04/08/2015 07:22 AM, Andrew Pennebaker wrote: > PostgreSQL uses a :colon syntax for parameterizing SQL commands with > command line variables. > > create-database.sql: > > CREATE DATABASE :db; > > Usage: > > $ psql -f create-database.sql -v db=test > > However, pgsanity/ecpg rejects these. > > $ pgsanity create-database.sql > line 1: ERROR: syntax error at or near ":db" > > Is there a flag I can give to ecpg to ignore input parameters? > > Is there a patch we could make to ecpg to accept input parameters? > > Is there another way to write my input parameters to work around this error? If I am following correctly: http://www.postgresql.org/docs/9.4/interactive/ecpg-variables.html > > -- > Cheers, > > Andrew Pennebaker > www.yellosoft.us <http://www.yellosoft.us> -- Adrian Klaver adrian.klaver@aklaver.com
Could you be more specific?
I can't find a relevant section to address my specific problem: ecpg complaining when I try to check the syntax of my .sql files that use input parameters.
I can't find a relevant section to address my specific problem: ecpg complaining when I try to check the syntax of my .sql files that use input parameters.
On Wed, Apr 8, 2015 at 9:34 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
If I am following correctly:On 04/08/2015 07:22 AM, Andrew Pennebaker wrote:PostgreSQL uses a :colon syntax for parameterizing SQL commands with
command line variables.
create-database.sql:
CREATE DATABASE :db;
Usage:
$ psql -f create-database.sql -v db=test
However, pgsanity/ecpg rejects these.
$ pgsanity create-database.sql
line 1: ERROR: syntax error at or near ":db"
Is there a flag I can give to ecpg to ignore input parameters?
Is there a patch we could make to ecpg to accept input parameters?
Is there another way to write my input parameters to work around this error?
http://www.postgresql.org/docs/9.4/interactive/ecpg-variables.html
--
Cheers,
Andrew Pennebaker
www.yellosoft.us <http://www.yellosoft.us>
--
Adrian Klaver
adrian.klaver@aklaver.com
--
Andrew Pennebaker <andrew.pennebaker@gmail.com> writes: > I can't find a relevant section to address my specific problem: ecpg > complaining when I try to check the syntax of my .sql files that use input > parameters. I'm not sure why you think that should work. psql and ecpg have quite distinct input languages. Both are extensions of SQL, but the key word there is "extension". ecpg certainly isn't going to accept psql's backslash commands for instance, any more than psql would accept ecpg's C code portions. And I doubt it would be useful for ecpg to simply ignore the variable-interpolation symbols; but it has no way to know what's going to be substituted for those symbols. It would be more interesting to consider giving psql a syntax-check-only mode; though I'm afraid use of variable interpolation would still be pretty problematic, since the variables are commonly filled from execution of previous commands. regards, tom lane
On 04/08/2015 01:38 PM, Andrew Pennebaker wrote: > Could you be more specific? > > I can't find a relevant section to address my specific problem: ecpg > complaining when I try to check the syntax of my .sql files that use > input parameters. I think this has more to do with pgsanity: https://github.com/markdrago/pgsanity "So the approach that PgSanity takes is to take a file that has a list of bare SQL in it, make that file look like a C file with embedded SQL, run it through ecpg and let ecpg report on the syntax errors of the SQL." A quick look through: https://github.com/markdrago/pgsanity/blob/master/pgsanity/sqlprep.py which is where the passed in SQL string is processed into ecpg format does not show that it deals with variables. So you might to take this up with the pgsanity author. > > On Wed, Apr 8, 2015 at 9:34 AM, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 04/08/2015 07:22 AM, Andrew Pennebaker wrote: > > PostgreSQL uses a :colon syntax for parameterizing SQL commands with > command line variables. > > create-database.sql: > > CREATE DATABASE :db; > > Usage: > > $ psql -f create-database.sql -v db=test > > However, pgsanity/ecpg rejects these. > > $ pgsanity create-database.sql > line 1: ERROR: syntax error at or near ":db" > > Is there a flag I can give to ecpg to ignore input parameters? > > Is there a patch we could make to ecpg to accept input parameters? > > Is there another way to write my input parameters to work around > this error? > > > If I am following correctly: > > http://www.postgresql.org/__docs/9.4/interactive/ecpg-__variables.html > <http://www.postgresql.org/docs/9.4/interactive/ecpg-variables.html> > > > -- > Cheers, > > Andrew Pennebaker > www.yellosoft.us <http://www.yellosoft.us> <http://www.yellosoft.us> > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > > > > > -- > Cheers, > > Andrew Pennebaker > www.yellosoft.us <http://www.yellosoft.us> -- Adrian Klaver adrian.klaver@aklaver.com
Makes sense.
Yes, it would be great if psql offered a flag for validating syntax. Other programming languages do this, for example, bash -n, ruby -c, and php -l.
Yes, it would be great if psql offered a flag for validating syntax. Other programming languages do this, for example, bash -n, ruby -c, and php -l.
On Wed, Apr 8, 2015 at 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Pennebaker <andrew.pennebaker@gmail.com> writes:
> I can't find a relevant section to address my specific problem: ecpg
> complaining when I try to check the syntax of my .sql files that use input
> parameters.
I'm not sure why you think that should work. psql and ecpg have quite
distinct input languages. Both are extensions of SQL, but the key word
there is "extension". ecpg certainly isn't going to accept psql's
backslash commands for instance, any more than psql would accept ecpg's
C code portions. And I doubt it would be useful for ecpg to simply ignore
the variable-interpolation symbols; but it has no way to know what's going
to be substituted for those symbols.
It would be more interesting to consider giving psql a syntax-check-only
mode; though I'm afraid use of variable interpolation would still be pretty
problematic, since the variables are commonly filled from execution of
previous commands.
regards, tom lane
--
On 04/09/2015 07:12 AM, Andrew Pennebaker wrote: > Makes sense. > > Yes, it would be great if psql offered a flag for validating syntax. > Other programming languages do this, for example, bash -n, ruby -c, and > php -l. Or pgsanity could take this: CREATE DATABASE :db; and convert it into: CREATE DATABASE db; before submitting it for syntax checking. The basic issue is whose syntax are you interested in checking, SQL or the program that is creating the SQL. If it is just the SQL end result, then it needs to rendered down to an actual valid SQL statement. If it is the program, then it gets complicated in a hurry. Already you have mentioned psql and ecpg. Then in Postgres there are various procedural languages that have their own way of creating SQL. Then there are external languages, for example the one I use a lot Python. It has its own method(s) of passing in variable information via DB-API. > > On Wed, Apr 8, 2015 at 3:53 PM, Tom Lane <tgl@sss.pgh.pa.us > <mailto:tgl@sss.pgh.pa.us>> wrote: > > Andrew Pennebaker <andrew.pennebaker@gmail.com > <mailto:andrew.pennebaker@gmail.com>> writes: > > I can't find a relevant section to address my specific problem: ecpg > > complaining when I try to check the syntax of my .sql files that use input > > parameters. > > I'm not sure why you think that should work. psql and ecpg have quite > distinct input languages. Both are extensions of SQL, but the key word > there is "extension". ecpg certainly isn't going to accept psql's > backslash commands for instance, any more than psql would accept ecpg's > C code portions. And I doubt it would be useful for ecpg to simply > ignore > the variable-interpolation symbols; but it has no way to know what's > going > to be substituted for those symbols. > > It would be more interesting to consider giving psql a syntax-check-only > mode; though I'm afraid use of variable interpolation would still be > pretty > problematic, since the variables are commonly filled from execution of > previous commands. > > regards, tom lane > > > > > -- > Cheers, > > Andrew Pennebaker > www.yellosoft.us <http://www.yellosoft.us> -- Adrian Klaver adrian.klaver@aklaver.com