PL\Pgsql Custom Function Error

Hi, I'm practicing writing PL\pgsql custom function using pgadminIII



This is the automatically generated code after setting the configuration options in the "New Function" popup

Code:

---

CREATE OR REPLACE FUNCTION "getID"(tname character varying, tarname character varying)
  RETURNS integer AS
$BODY$BEGIN
SELECT id FROM tname WHERE name=tarname;
END$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION "getID"(character varying, character varying)
  OWNER TO postgres;

---

Error Generated

Code:

---

ERROR:  function getid(unknown, unknown) does not exist
LINE 1: SELECT getID('test','lliok');
              ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

********** Error **********

ERROR: function getid(unknown, unknown) does not exist
SQL state: 42883
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Character: 8

---

attempt to call function from query window within pgadminIII, that generated error

Code:

---

SELECT getID('test','Biggie');

---

Tried this too

Code:

---

SELECT "getID"('test','Biggie');

---

but got this error

Code:

---

ERROR:  relation "tname" does not exist
LINE 1: SELECT id FROM tname WHERE name=tarname
                      ^
QUERY:  SELECT id FROM tname WHERE name=tarname
CONTEXT:  PL/pgSQL function "getID" line 2 at SQL statement

********** Error **********

ERROR: relation "tname" does not exist
SQL state: 42P01
Context: PL/pgSQL function "getID" line 2 at SQL statement

---

any suggestions? thanks

Is it allowed to use character varying type without the length? According to the docs ( http://www.postgresql.org/docs/9.1/s...character.html ) this type requiers the length to be set:

Quote:

---

Code:

---

character varying(n)

---

---

Quote:

---

Originally Posted by VictorN

Is it allowed to use character varying type without the length? According to the docs ( http://www.postgresql.org/docs/9.1/s...character.html ) this type requiers the length to be set:

---

thanks for replying

I read that page before, the following sentences suggest I should be ok

Code:

---


 If character varying is used without length specifier, the type accepts strings of any size.The latter is a PostgreSQL extension.

---

The last sentence was somewhat confusing, does it mean that I need to extend PostgreSQL in order to achieve that behavior? I would assume the term standard being used if that behavior was the norm

It was suggested to me to use Dynamic Queries (http://www.postgresql.org/docs/curre...-EXECUTING-DYN)
Got it working and it's more efficient than what I originally wrote in my code