Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    4

    Unanswered: PL/pgSQL function not working

    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.

  2. #2
    Join Date
    May 2008
    Posts
    277
    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.

  3. #3
    Join Date
    Jun 2011
    Posts
    4

    Still does not work

    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;

  4. #4
    Join Date
    May 2008
    Posts
    277
    Quote Originally Posted by valandil View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •