[Momentum]

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

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

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

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

Tried this too

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

but got this error

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

This post has been edited by Atli: 27 August 2012 - 09:34 PM
Reason for edit:: Moved to the Database forum.

[Atli]

For the first error, it seems like there may be some weirdness regarding the case when you define the function using quotes, and call it without them. - Identifiers in PL/pgSQL are supposed to be case-insensitive, but I'm guessing that when you use quotes around the identifier during creation, that it saves the function name exactly as written, case and all. So when you call it like SELECT getID(...), Postgres looks for the function in a case-insensitive manner, and thus doesn't find it. But when you do: SELECT "getID"(...), it looks for it in a more exact manner, and finds it.

You need either lose the quotes in the function definition, making it case-insensitive, or make a note to always quote the function name when you want to use it.


As for the second error, that is quote clear. You are trying to use the tname input parameter as the table name for the query, but it can't be done like that. The FROM tname clause simply looks for a table named "tname", rather than passing on the parameter value. - You can get around this by using Dynamic Queries. Those are queries compiled as strings and executed using the EXECUTE command.

For example, try changing the body of your function to something like this:

DECLARE
    outID INTEGER;
BEGIN
    EXECUTE 'SELECT id FROM ' || tname || ' WHERE name=$1'
        INTO outID
        USING tarname;
    RETURN outID;
END

As you can see, I compile a string, concating "tname" into it as the table name, and execute it so that the result goes INTO the "outID" variable, USING the "tarname" parameter where the "$1" placeholder is in the command string.

(Note that this is untested. I haven't used this stuff in a while, so I'm not 100% sure my syntax is accurate.)

[Momentum]

thanks Atli for the explanation and the link. Your suggestion worked and this approach is more efficient.