Hello I propose to add possibility to use psql variables as real query parameters. The goal of this proposal is simplification of creating psql based commands. Current using of psql variables based on substitution has large area of using, but has some risks. a) there are possible sql injection, b) we have to have to do some special (not too much readable quoting) - see Bruce's book, psql chapter.
I checked, so this doesn't need much work. Attachment contains a prototype. [pa...@nemesis ~]$ echo "select upper(:message)" | psql -r -v message="Pavel's cat" postgres upper ───────────── PAVEL'S CAT (1 row) [pa...@nemesis ~]$ psql -v message="Pavel's cat" postgres psql (8.5devel) Type "help" for help. postgres=# \pexec Separately passing parameters is on. postgres=# select upper(:message); upper ───────────── PAVEL'S CAT (1 row) This small feature simplify integration psql to shell environment. comments, notes?? Regards Pavel Stehule
*** ./command.c.orig 2009-10-13 23:04:01.000000000 +0200 --- ./command.c 2009-11-15 21:53:25.418639611 +0100 *************** *** 1127,1132 **** --- 1127,1152 ---- free(pattern); } + /* \pexec -- pass parameters separately */ + else if (strcmp(cmd, "pexec") == 0) + { + char *opt = psql_scan_slash_option(scan_state, + OT_NORMAL, NULL, false); + + if (opt) + pset.use_parameters = ParseVariableBool(opt); + else + pset.use_parameters = !pset.use_parameters; + if (!pset.quiet) + { + if (pset.use_parameters) + puts(_("Separately passing parameters is on.")); + else + puts(_("Separately passing parameters is off.")); + } + free(opt); + } + /* \! -- shell escape */ else if (strcmp(cmd, "!") == 0) { *** ./common.c.orig 2009-04-11 20:38:54.000000000 +0200 --- ./common.c 2009-11-15 22:28:25.036648416 +0100 *************** *** 852,858 **** if (pset.timing) INSTR_TIME_SET_CURRENT(before); ! results = PQexec(pset.db, query); /* these operations are included in the timing result: */ ResetCancelConn(); --- 852,876 ---- if (pset.timing) INSTR_TIME_SET_CURRENT(before); ! if (!pset.use_parameters) ! results = PQexec(pset.db, query); ! else ! { ! /* use PQexecParams function instead */ ! results = PQexecParams(pset.db, query, ! pset.nparameters, ! NULL, ! pset.parameters, ! NULL, ! NULL, ! 0); ! if (pset.nparameters) ! { ! pset.nparameters = 0; ! pset.maxparameters = 0; ! free(pset.parameters); ! } ! } /* these operations are included in the timing result: */ ResetCancelConn(); *** ./psqlscan.l.orig 2009-11-15 21:28:55.000000000 +0100 --- ./psqlscan.l 2009-11-15 22:06:04.814641928 +0100 *************** *** 693,701 **** if (value) { ! /* It is a variable, perform substitution */ ! push_new_buffer(value); ! /* yy_scan_string already made buffer active */ } else { --- 693,716 ---- if (value) { ! if (pset.use_parameters) ! { ! char buffer[10]; ! ! /* add new parameter */ ! if (pset.nparameters == pset.maxparameters) ! { ! pset.maxparameters += 100; ! pset.parameters = malloc(sizeof(char *) * pset.maxparameters); ! } ! pset.parameters[pset.nparameters++] = value; ! sprintf(buffer, "$%d", pset.nparameters); ! push_new_buffer(buffer); ! } ! else ! /* It is a variable, perform substitution */ ! push_new_buffer(value); ! /* yy_scan_string already made buffer active */ } else { *** ./settings.h.orig 2009-02-26 17:02:38.000000000 +0100 --- ./settings.h 2009-11-15 21:54:23.321640498 +0100 *************** *** 111,116 **** --- 111,120 ---- const char *prompt2; const char *prompt3; PGVerbosity verbosity; /* current error verbosity level */ + bool use_parameters; + int nparameters; + int maxparameters; + const char **parameters; } PsqlSettings; extern PsqlSettings pset; *** ./startup.c.orig 2009-04-05 06:19:58.000000000 +0200 --- ./startup.c 2009-11-15 22:45:02.654643678 +0100 *************** *** 122,127 **** --- 122,131 ---- pset.queryFoutPipe = false; pset.cur_cmd_source = stdin; pset.cur_cmd_interactive = false; + pset.use_parameters = false; + pset.parameters = NULL; + pset.maxparameters = 0; + pset.nparameters = 0; /* We rely on unmentioned fields of pset.popt to start out 0/false/NULL */ pset.popt.topt.format = PRINT_ALIGNED; *************** *** 322,327 **** --- 326,332 ---- {"port", required_argument, NULL, 'p'}, {"pset", required_argument, NULL, 'P'}, {"quiet", no_argument, NULL, 'q'}, + {"pexec", no_argument, NULL, 'r'}, {"record-separator", required_argument, NULL, 'R'}, {"single-step", no_argument, NULL, 's'}, {"single-line", no_argument, NULL, 'S'}, *************** *** 346,352 **** memset(options, 0, sizeof *options); ! while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:HlL:no:p:P:qR:sStT:U:v:VwWxX?1", long_options, &optindex)) != -1) { switch (c) --- 351,357 ---- memset(options, 0, sizeof *options); ! while ((c = getopt_long(argc, argv, "aAc:d:eEf:F:h:HlL:no:p:P:qrR:sStT:U:v:VwWxX?1", long_options, &optindex)) != -1) { switch (c) *************** *** 432,437 **** --- 437,445 ---- case 'q': SetVariableBool(pset.vars, "QUIET"); break; + case 'r': + pset.use_parameters = true; + break; case 'R': pset.popt.topt.recordSep = pg_strdup(optarg); break;
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers