Trying to create an Insert function

Question

I'm trying to create a function in Postgres 8.4 using pgAdmin and I have the following code

CREATE OR REPLACE FUNCTION spcwriteperson(IN fname varchar(20))
  RETURNS VOID AS
$BODY$
    INSERT INTO person ("firstName") VALUES(fname);
$BODY$
  LANGUAGE sql VOLATILE
  COST 100;

when I try to run this, it complains that fname at VALUES(fname) is not a column. I'm coming from writing sprocs and functions in MySQL and Sql Server. Any help on why this doesn't work, or what I am doing wrong?

Answer 1

If you don't like using numbered parameters, you can make use of PL/PGSQL:

CREATE OR REPLACE FUNCTION spcwriteperson(fname varchar(20)) RETURNS VOID AS
$$
BEGIN
    INSERT INTO person (firstName) VALUES (fname);
END
$$
  LANGUAGE 'plpgsql';

PL/PGSQL will also give you a language more like SQL Server's T-SQL. PL/PGSQL has control structures, variables, and all of that fun stuff. PostgreSQL's SQL language implementation is much more strict about what functionality is available - it's a great implementation of SQL, but it's a querying language, not a procedural programming language.

Answer 2

SQL-language functions' parameters are numbered, $1, $2, and so on. See the documentation for details. You could use pgSQL if you want named parameters and more advanced capabilities.