PL/pgSQL function not working

valandil · #1 (**permalink**) 06-16-11, 15:08

Hello!

I'm trying to write a simple function in PL/pgSQL that simply inserts two rows in two different tables. However, I receive a puzzling error. Here's the function:

Code:

CREATE OR REPLACE FUNCTION "f_newEmployee"(l_name varchar(32), f_name varchar(32), idaff integer, b_date date)
RETURNS void AS $$

BEGIN

INSERT INTO "TB_Employe" ("IndexEmploye", "EMP_Nom", "EMP_Prenom", "EMP_Active") VALUES (DEFAULT, 'l_name', 'f_name', DEFAULT);
INSERT INTO "TB_Emp2Aff" ("IndexEmp2Aff", "EMP2_IndexEmploye", "EMP2_IndexAffiliation", "EMP2_BeginDate") VALUES (DEFAULT, curval('"sq_IndexEmploye"'::regclass), idaff, 'begindate');

END;

$$ LANGUAGE plpgsql;

and here's hte error:

Code:

select "f_newEmployee"(Smith, John, 2, now());
ERROR:  column "smith" does not exist
LINE 1: select "f_newEmployee"(Smith, John, 2, now());

I really don't know what's going on. I've tried several quotation marks, still nothing.

futurity · #2 (**permalink**) 06-17-11, 03:30

First off, if you simply lower-cased all your identifiers, you wouldn't have to worry about quoting them. However, if you wish to have them mixed-cased, they always need to be double-quoted, otherwise don't quote them at all. String literals, on the other hand, always need to be single-quoted.

So in your function, unless you want to literally insert 'l_name', 'f_name', and 'begindate' into every row in your tables, they do not need to be quoted (although if you really want to, you can double-quote them). Also, the variable begindate does not exist; I'm pretty sure you mean b_date.

However, the error you're seeing is being caused by your SELECT statement. 'Smith' and 'John' are string literals and need to be single-quoted.

valandil · #3 (**permalink**) 06-17-11, 08:53

I want my identifiers to be mixed-case. My DB has capitalization everywhere (makes the fields more readable).

Anyhow, I already tried what you said, and it doesn't work. When I single quote 'Smith' and 'John', psql says that it cannot find the function because it does not have the appropriate argument types. My function takes

(varchar, varchar, integer, date)

but when I single quote my first two fields, it tries to find a function

(unknown, unknown, integer, date).

I don't understand why it doesn't work. Every example with this kind of function seems to work.

Code:

BEGIN

INSERT INTO "TB_Employe" ("IndexEmploye", "EMP_Nom", "EMP_Prenom", "EMP_Active") VALUES (DEFAULT, l_name, f_name, DEFAULT);
INSERT INTO "TB_Emp2Aff" ("IndexEmp2Aff", "EMP2_IndexEmploye", "EMP2_IndexAffiliation", "EMP2_BeginDate") VALUES (DEFAULT, curval('"sq_IndexEmploye"'::regclass), idaff, b_date);

END;

futurity · #4 (**permalink**) 06-17-11, 11:46

Quote:

Originally Posted by valandil

Code:

select "f_newEmployee"(Smith, John, 2, now());

now() returns a timestamp, your function takes a date. Use CURRENT_DATE instead, which is also standard SQL.

Internet Services

Web Development

Digital Marketing

Consumer Tech