Error on INSERT INTO

hello:

I am getting an error on a simple INSERT INTO within a function using POSTGRES 8.2. I have tried several variations in order to get some thing to work. But no success.

Please be kind and take a look at the following

CREATE OR REPLACE FUNCTION bmtrades(fundinst_bm integer, fundinst_ma integer, tdate date)
RETURNS character AS
$BODY$

DECLARE

r_str char(8);

BEGIN
select rand_str() into r_str;

insert into temp_bm(unqkey, secid_sort, bm_fundinstance, bm_pbaccount, tdate, bm_secid, bm_secdesc, bm_qty, bm_price, bm_buysell, bm_isocurr)
select r_str, cast(t.securitykey as text)||'BM', fundinstancekey, pbaccount, transactiondate , t.securitykey, s.securitydesc, qty, price, buy_sell, currisocode
from transaction t, security s
where t.fundinstancekey = fundinst_bm and t.transactiondate = tdate and
t.securitykey = s.securitykey;

--(unqkey, secid_sort, bm_fundinstance, bm_pbaccount, tdate, bm_secid, bm_secdesc, bm_qty, bm_price, bm_buysell, bm_isocurr)

insert into temp_bm
select r_str, cast(t.securitykey as text)||'MA', fundinstancekey, pbaccount, transactiondate , t.securitykey, s.securitydesc, qty, price, buy_sell, currisocode
from transaction t, security s
where t.fundinstancekey = fundinst_ma and t.transactiondate = tdate and
t.securitykey = s.securitykey;



return r_str;
END


$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION bmtrades(bm_finst integer, ma_finst integer, tdate date) OWNER TO postgres;

when I complie I get the following:


ERROR: syntax error at or near "$1"
SQL state: 42601
Context: SQL statement in PL/PgSQL function "bmtrades" near line 13

Tables are defined as follows:

CREATE TABLE temp_bm
(
secid_sort character(15),
bm_secid integer,
bm_secdesc character(25),
bm_qty integer,
bm_price numeric(19,6),
bm_ratio numeric(10,6),
ma_secid integer,
ma_qty integer,
ma_price numeric(19,6),
unqkey character(8),
bm_fundinstance integer,
ma_fundinstance integer,
tdate date,
bm_pbaccount character(25),
ma_pbaccount character(25),
bm_buysell character(1),
ma_buysell character(1),
bm_isocurr character(3),
ma_isocurr character(3)
)
WITHOUT OIDS;
ALTER TABLE temp_bm OWNER TO postgres;



CREATE TABLE "transaction"
(
transactionkey serial NOT NULL,
securitykey integer,
fundinstancekey integer,
qty integer,
price numeric(20,6),
transactiondate date,
entrydate date,
sourcekey integer,
taskrunkey integer NOT NULL,
buy_sell character(1),
currisocode character(3),
pbaccount character(25),
CONSTRAINT pk_transaction PRIMARY KEY (transactionkey),
CONSTRAINT fk_transaction_fundinstance FOREIGN KEY (fundinstancekey)
REFERENCES fundinstance (fundinstancekey) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_transaction_securitykey FOREIGN KEY (securitykey)
REFERENCES "security" (securitykey) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_transaction_source FOREIGN KEY (sourcekey)
REFERENCES source (sourcekey) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_transaction_taskrun FOREIGN KEY (taskrunkey)
REFERENCES taskrun (taskrunkey) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITHOUT OIDS;
ALTER TABLE "transaction" OWNER TO postgres;



Thanks for your assistance.

Best regards

kd

The name tdate is used as a parameter and as a column-name as well. That explains the error-message.

By the way, the function is hard to read with all SQL-statements in lowercase... I prefer SQL in UPPERCASE and my table- and column-names in lowercase. Makes it easier to understand and debug.

Thanks for being so observant. A second pair of eyes always helps.

But I wonder why it was bauking about $1.

kd

I agree, a little bit weird it's complaining about the first parameter ($1) when you have a problem with the third one. $3 would make sense.

This was the message in pgAdmin3:

That's how I found the bug, the column-name between bm_pbaccount and bm_secid is replaced with a parameter. And that couldn't be right.