2 Replies - 1895 Views - Last Post: 28 August 2012 - 07:20 AM Rate Topic: -----

#1 Momentum  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 29
  • Joined: 18-October 09

PL\pgsql Custom function error

Posted 27 August 2012 - 08:34 PM

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.


Is This A Good Question/Topic? 0
  • +

Replies To: PL\pgsql Custom function error

#2 Atli  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 3712
  • View blog
  • Posts: 5,964
  • Joined: 08-June 10

Re: PL\pgsql Custom function error

Posted 27 August 2012 - 09:33 PM

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.)
Was This Post Helpful? 1
  • +
  • -

#3 Momentum  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 29
  • Joined: 18-October 09

Re: PL\pgsql Custom function error

Posted 28 August 2012 - 07:20 AM

thanks Atli for the explanation and the link. Your suggestion worked and this approach is more efficient.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1