3

I have written a pgsql function along the lines of what's shown below. How can I get rid of the $1, $2, etc. and replace them with the real argument names to make the function code more readable?

Regards

Peter

CREATE OR REPLACE FUNCTION InsertUser (
    UserID UUID,
    FirstName CHAR(10),
    Surname VARCHAR(75),
    Email VARCHAR(75)
)
RETURNS void
AS
$$
INSERT INTO "User" (userid,firstname,surname,email)
VALUES ($1,$2,$3,$4)
$$
LANGUAGE SQL;

2 Answers 2

5

You must declare it before you use it at the declare area. For exemple :

DECLARE   
    v_UserID alias for $1;
    v_FirstName alias for $2;
    v_Surname alias for $3;
    v_Email alias for $4;
BEGIN
$$
END
0
1

Try this thing

    CREATE or replace FUNCTION delhi(nam varchar, mm numeric , nn numeric  ) RETURNS integer
    AS $$
        begin
        insert into exe ( name , m1 ,m2 )  values ( nam, mm , nn );
-- see here column name is not like function argument name , so that it wont say error
           return 1;
    end ;
    $$
    LANGUAGE plpgsql;

Function calling :

select delhi ( 'first value', 2,3 ) ; 
4
  • ok that seems to work. But not sure why?? All I see that's materially different is that you excluded the explicit column names in the insert statement. Can one not use explicit column names? Commented Mar 19, 2010 at 4:57
  • and how come it works for you not enclosing the table name in quotes "? When I try Insert into user PGAdmin complains. I need to say Insert into "User" Commented Mar 19, 2010 at 4:59
  • See, you can give the column name explicit , But that should not be function argument name . If you have function argument name as column name . that is interpret as $1, $2 . Commented Mar 19, 2010 at 5:08
  • Peter, the keyword "User" is reserved in Postgres, as a result you must double quote it to use it as a table. Commented Sep 16, 2010 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.