2

Friends:

Have been trying to parameter-ize into a query in pl/python - and am surely missing something simple here; I've tried % and $ prepends to the variable name, with no luck.

(also haven't been able to pass a result variable into the python log - but this is a diff problem! I do have the log set up - can sent literal strings to it - but have cut out lots of code for clarity here)

CREATE OR REPLACE FUNCTION footest0 (param_key integer) RETURNS text AS $$

# 1) SELECT from record based on parameter param_key:

rv = plpy.execute("SELECT name_key,address_key FROM file WHERE foreign_key = param_key)

name = rv[0]["name"]
address = rv[0]["address"]

# how to put results into the log?:
logging.info('  record: %(case)s')

$$ LANGUAGE plpythonu;
DrLou
  • 649
  • 5
  • 21

2 Answers2

3

Use the $n notation:

st = "SELECT name_key,address_key FROM file WHERE foreign_key = $1"
pst = plpy.prepare(st, [ "integer" ])
rv = plpy.execute(pst, [ param_key ])
Paulo Scardine
  • 73,447
  • 11
  • 124
  • 153
  • Paulo - exactly what I needed! I wasn't quite getting that 'integer' there is the input format, not its actual value. Grazie! – DrLou Jun 19 '11 at 17:47
2

I'm not sure exactly what you're asking but if you're trying to parametrize the columns you're using to control a join on a query, parameters don't work that way.

If you're really trying to do that, you can always use string formatting to create your SQL query, like the following:

rv = plpy.execute("SELECT name_key, address_key FROM file WHERE %s = %s" %
                  (foreign_key, param_key))

If you're simply looking to compare foreign_key to a string, Paulo has the answer you're looking for.

ironchefpython
  • 3,409
  • 1
  • 19
  • 32
  • 1
    what if `param_key == "2351 AND 1=1"` or `param_key == "2351; UPDATE auth_user SET password='0w3d' WHERE username='admin'"`? Dangerous stuff! The `$n` notation will handle security issues with proper quotation. – Paulo Scardine Jun 17 '11 at 05:06
  • You're absolutely right, if the data is coming from a user, it needs to be sanitized before it can be used for string formatting. – ironchefpython Jun 17 '11 at 05:16