-1

I have a function that in theory will give the country name as output when the input is the city name:

CREATE OR REPLACE FUNCTION public.country_by_city(city varchar, OUT Country varchar)  
RETURNS varchar  
LANGUAGE SQL AS 
$function$   
    SELECT c.country     
    FROM country c   
    JOIN city c2 ON c2.country_id = c.country_id     
    WHERE c2.city = $1   
$function$ 
;

And it's working if I will change c2.city to c2.city_id but when I am trying to call a function with a varchar column in it, for example :

SELECT public.country_by_city(Abha);

I get this error:

SQL Error [42703]: ERROR: column "abha" does not exist

I tried to use UPPER() or INITCAP(), and it didn't help. It's worth mentioning that city name records in the city table have capitalized first letters.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
lowercase male
  • 11
  • 4

1 Answers1

2

You need to call this parameter using simple quotes, for example:

SELECT public.country_by_city('Abha');
Anthony Sotolongo
  • 1,395
  • 2
  • 9
  • 17
  • Jesus Christ, what a stupid mistake, thank you, it worked. But if I will write for example: `SELECT public.country_by_city(:city);` it will return the same error, what should I change in that case? – lowercase male Mar 28 '23 at 11:14
  • This parameter required simple quotes, just that, this way `:city` maybe is something related to your driver and variable binding – Anthony Sotolongo Mar 28 '23 at 11:22