PostgreSQL issue with named arguments in a function

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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