0

I'm learning PosgreSQL, i'm trying to call function get_color in a SELECT statement inserting the column name of the table as parameter, but PostgreSQL version 9.6 (i've tried on 13.0 with the same result) returns me an UndefinedFunction error.

Here's the complete query code:

SELECT n 
FROM pianokeys pn,
LATERAL get_color(pn.n) AS res;

CREATE OR REPLACE FUNCTION get_color(n integer) RETURNS text AS $$

  BEGIN
  
    IF((n % 88) % 2)
    
    THEN
    
       RETURN $b$black$b$;
    
    ELSE
    
       RETURN $w$white$w$;
       
    END IF;
       
  END;
  $$
  
  LANGUAGE plpgsql;

`

Here's the compiler error:

There was an error with the SQL query:

PG::UndefinedFunction: ERROR:  function get_color(integer) does not exist
LINE 8: lateral get_color(pn.n) as res) AS "t1" LIMIT 1
                ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

What am i missing?

EDOARDO ROSSO
  • 1
  • 3
  • Version 9.6 is EOL, waste of time to work on that version. Did you create the function before the select statement? – Frank Heikens Dec 05 '22 at 20:40
  • [Works just fine](https://dbfiddle.uk/ZJeSEa8M) - but you don't need the lateral part. `select n, get_color(n) from pianokeys` will work just as well. –  Dec 05 '22 at 20:40
  • As written you are using the function before you create it. You need to do `CREATE OR REPLACE FUNCTION get_color` first. FYI, you don't need to $ quote the return values, just do `'black'`. – Adrian Klaver Dec 05 '22 at 20:41
  • The error message doesn't seem to match your code. It mentions a `as t1 limit 1` which is not part of your question. –  Dec 05 '22 at 20:45
  • @a_horse_with_no_name Pls post your comment as an answer so i can tick it as the resolving one. – EDOARDO ROSSO Dec 05 '22 at 20:57

0 Answers0