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?

SimonJ · Accepted Answer · 2009-11-30 01:05:28Z

3

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.

edited Nov 30, 2009 at 1:05

answered Nov 30, 2009 at 1:00

SimonJ

21.3k2 gold badges38 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Craig Over a year ago

That fixed my problem. That smells kinda bugish no? Neither Oracle or SQL Server have a problem with that format. Thanks a lot SimonJ!

SimonJ Over a year ago

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 Over a year ago

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...

Erwin Brandstetter · Accepted Answer · 2011-10-14 03:45:28Z

1

This behavior will be changed in PostgreSQL 9.0.

a) PostgreSQL 9.0 is smarter and applies variables only at the 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.

edited Oct 14, 2011 at 3:45

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Nov 30, 2009 at 6:28

Pavel Stehule

111 silver badge1 bronze badge

Comments

bluish · Accepted Answer · 2015-01-29 14:03:53Z

0

name is a reserved keyword in PostgreSQL.

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

Also what he said: prepend all your variable names with a _ or similar.

edited Jan 29, 2015 at 14:03

bluish

27.5k28 gold badges126 silver badges185 bronze badges

answered Nov 30, 2009 at 1:02

richo

9,0493 gold badges33 silver badges47 bronze badges

Collectives™ on Stack Overflow

PostgreSQL issue with named arguments in a function

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related