views:

119

answers:

3

PostgreSQL 8.4 on Linux -

I have a function -

CREATE OR REPLACE FUNCTION production.add_customer (  
name varchar(100),  
email_address varchar(300),  
street_address text,  
city varchar(50),  
state varchar(2),  
zip varchar(10),  
secret1 bytea,  
secret2 bytea,   
secret3 bytea,  
secret4 bytea,  
referrer text)  
RETURNS integer as $$  
BEGIN  
INSERT INTO customers (name, email_address, street_address, city, state, zip, secret1, secret2, secret3, secret4, create_date, referrer) VALUES  
(name, email_address, street_address, city, state, zip, create_date, referrer
pgp_sym_encrypt(secret1, 'reallylongrandomstring'),   
pgp_sym_encrypt(secret2, 'reallylongrandomstring'),   
pgp_sym_encrypt(secret3, 'reallylongrandomstring'),   
pgp_sym_encrypt(secret4, 'reallylongrandomstring'),   
current_timestamp, referrer);  
RETURNING customer_id;  
END;  
$$ LANGUAGE plpgsql;

That is returning this error when I try an create it -

ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO customers ( $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  ...
                            ^
QUERY:  INSERT INTO customers ( $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 ,  $8 ,  $9 ,  $10 , create_date,  $11 ) VALUES ( $1 ,  $2 ,  $3 ,  $4 ,  $5 ,  $6 , create_date,  $11  pgp_sym_encrypt( $7 , 'reallylongrandomstring'), pgp_sym_encrypt( $8 , 'reallylongrandomstring'), pgp_sym_encrypt( $9 , 'reallylongrandomstring'), pgp_sym_encrypt( $10 , 'reallylongrandomstring'), current_timestamp,  $11 )
CONTEXT:  SQL statement in PL/PgSQL function "add_customer" near line 8
myserver=#

I have tried ALIAS FOR, no luck. Ideas?

Thanks,

Craig

+2  A: 

Your arguments have the same name as the columns in the INSERT; PL/pgSQL is incorrectly substituting both sets of identifiers (columns and values) with the argument, ending up with the nonsensical INSERT you see in the error.

See the Caution section on this page for the official word - but essentially you'll need to change the argument names.

SimonJ
That fixed my problem. That smells kinda bugish no? Neither Oracle or SQL Server have a problem with that format. Thanks a lot SimonJ!
Craig
I'm not actually sure they consider it a bug, given how much discussion is dedicated to the subject in the docs. If nothing else, it encourages good practice by forcing non-overlapping names!
SimonJ
Oh, and it's quite possible to get into a similar (but maybe more subtle) mess with Oracle too - I've seen innocent `SELECT` queries suddenly break when someone introduced either a local variable with the same name as a column, or vice versa...
SimonJ
A: 

name is a reserved keyword in postgres.

Change it to INSERT INTO customers ("name", etc etc

^^ Also what he said, prepend all your variable names with a _ or somesuch.

Richo
A: 

This behave will be changed in PostgreSQL 8.5.

a) PostgreSQL8.5 is smart and apply variables only on right places,

b) priority is optional - like PostgreSQL, like Oracle or raise exception

for older version, use prefix for all local variables and parameters in every functions with integrated SQL

Pavel Stehule