CODEWITHSUNDEEP
pythonpostgresql
Amar
Amar

Reputation: 341

plpython function to update a row is not working

I am using PostgreSQL 9.4.7 and Python 2.7.6. I am writing a plpython function to update a row in user table and my code is as below -

CREATE FUNCTION update_user(myid int, mymail text, myname text) RETURNS text AS $$
    from plpy import spiexceptions
    plan=plpy.prepare("UPDATE auth_user SET email=$2, username=$3 WHERE id = $1",
    ["int"] ["text"]["text"])
    rv=plpy.execute(plan, [myid,myemail,myusername])
    return rv
    $$ LANGUAGE plpythonu;

I am able to create this function successfully in postgres DB but while I am trying to execute it via below command on postgres shell-
select update_user(1,"[email protected]@sifymail.com","updatedname");
I am getting following error -
ERROR: column "[email protected]" does not exist
LINE 1: select update_user(1,"[email protected]","hell");
Can someone point me where am I making a mistake?

Upvotes: 1

Views: 657

Answers (1)

Ildar Musin
Ildar Musin

Reputation: 1468

First of all, string literals in postgresql must be surrounded by single quotes, not the double ones:

select update_user(1,'[email protected]@sifymail.com','updatedname');

Double quotes are used to refer columns.

Second. The arguments list in your prepare function isn't correct python list. It should look like:

plpy.prepare("UPDATE auth_user SET email=$2, username=$3 WHERE id = $1",
             ["int", "text", "text"])

Upvotes: 2

Related Questions